How do I create a form in excel for mac and windows7

October 24, 2018 at 01:09:18
Specs: Windows 7 /Mac
I need to create a form (Using Excel?) That contains a grid of boxes. I want the cursor to automatically jump to the next box when a single digit entered in the first box. I realize this will probably involve a macro but I cant seem to find one online.
Is this possible?

See More: How do I create a form in excel for mac and windows7

Reply ↓  Report •

#1
October 24, 2018 at 01:30:47
I think you should have asked this under software --> office, but im gonna wait and see it DerbyDad03 responds :)

personally i think it's possible in combination with VBA, but im not sure if it will also work with mac OS

i5-6600K[delid]@4.7GHz/4.3GHz@1.376v LLC=6 | 2x4GB Crucial-DDR4-2133CL15@14-14-14-28 1T 2700MHz@1.35v
MSI Armor RX 570 4GB@1375Mhz core@1.1v/2050MHz


Reply ↓  Report •

#2
November 20, 2018 at 06:18:09
This can be achieve with both a userform or a worksheet, formatting the sheets as a grid. Have you resolved this now? if not do you still require help with this?

Reply ↓  Report •

#3
November 26, 2018 at 02:57:34
Hi, in answer to your question have I solved the above problem? No.

Reply ↓  Report •

Related Solutions

#4
November 26, 2018 at 08:12:24
Ok, i’ll work on a solution for you tomorrow

Reply ↓  Report •

#5
November 26, 2018 at 12:57:56
Edit: This may work via a loop. I can test that later, but I'd first like to know if I'm even close to meeting your requirements, so let me know.

This is a brute force method, but it might get you what you want. It could be used to work only within a specific range, such as within your "grid".

The code example below was simplified for the following conditions:

1 - The "next cell selection" will only occur within a "grid range" of A1:A10.
2 - It only works for digits 0, 1 & 2.

It will need to be fleshed it out for a different/larger range and digits 3-9.

Note: Since OnKey is an Application level event, you need to make sure that it gets reset when the workbook is Closed. Please see Step 3 at the end of this post.

Step 1: Put this code in the Worksheet module for the sheet that contains your grid.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'Determine if selected cell is within the Grid Range (A1:A10)
  If Not Intersect(Target, Range("A1:A10")) Is Nothing Then

'Set OnKey events
    Application.OnKey "0", "Digit0_Sub"
    Application.OnKey "1", "Digit1_Sub"
    Application.OnKey "2", "Digit2_Sub"

    '... Repeat for digits 3-9

  Else

'If not, Reset all OnKey events
    Application.OnKey "0"
    Application.OnKey "1"
    Application.OnKey "2"

    '... Repeat for digits 3-9
  End If

End Sub

Step 2: Place these macros in a Regular module within the workbook:

Sub Digit0_Sub()
  On Error Resume Next
  ActiveCell = 0
  ActiveCell.Offset(1, 0).Activate
End Sub

Sub Digit1_Sub()
  On Error Resume Next
  ActiveCell = 1
  ActiveCell.Offset(1, 0).Activate
End Sub

Sub Digit2_Sub()
  On Error Resume Next
  ActiveCell = 2
  ActiveCell.Offset(1, 0).Activate
End Sub

'Repeat for digits 3-9

Step 3: Put this code in the ThisWorkbook module.

If the last cell that was selected is within the grid, then the SelectionChange code has set the OnKey events. If you close the workbook but leave Excel open, the OnKey method will not be reset. As a consequence, pressing any digit key in another workbook will cause Excel to automatically open the file with the OnKey macros. To prevent this from happening, you should include code in the Workbook_BeforeClose event code to reset the OnKey events.

In this case, something as simple as this will work since it will cause the SelectionChange macro to fire and reset the Onkey events.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'Select a cell outside of the Grid Range
  Sheets(1).Range("B1").Select

End Sub

If you don't want to physically (via the macro) select another cell before the workbook closes, you would need to explicitly disable the events via:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

 'Reset Onkey events
    Application.OnKey "0"
    Application.OnKey "1"
    Application.OnKey "2"

    '... Repeat for digits 3-9

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Ask Question