Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro for cell select
I need help with a macro, I have a sheet that only needs certin cells that need information. I would like for example, for the user to enter the information in cell a2, press enter, and move to cell f3, enter info, press enter, and move to cell b5. Note: these are not the cells i need, just an example. The basic code for this would be great and ican fill in the cells required. Once all the specified cells are filled in, I would like to have a window pop up and say its done. -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292 View this thread: http://www.excelforum.com/showthread...hreadid=475575 |
#2
|
|||
|
|||
Assign this code to the workbook to go to Sheet1 cell A2 every time the
file opens. Private Sub Workbook_Open() Range(A2).Select End Sub Assign this code to Sheet1. It checks to see if a change was made to specified cells, and moves to the next cell. Note the Target.Address cell references must be all caps, in this format: $A$2 Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$2" Then Range("F3").Select If Target.Address = "$F$3" Then Range("B5").Select If Target.Address = "$B$5" Then MsgBox "Done." End Sub |
#3
|
|||
|
|||
Sorry, left a line out:
Private Sub Workbook_Open() Sheets("Sheet1").Select Range(A2).Select End Sub |
#4
|
|||
|
|||
I have this titlle now for a different code, does it have to use this? Or can it be changed. Thus far I can not get it to work:( Private Sub Worksheet_Change(ByVal Target As Range) My code 'Template (Sheet 1)' which is my third sheet in workbook. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Target.Parent.Name = Target.Value End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Range("C2").Select If Target.Address = "$C$2" Then Range("C3").Select If Target.Address = "$C$3" Then Range("A6").Select If Target.Address = "$A$6" Then Range("B6").Select If Target.Address = "$B$6" Then Range("C6").Select If Target.Address = "$C$6" Then Range("D6").Select If Target.Address = "$D$6" Then Range("E6").Select If Target.Address = "$E$6" Then Range("C11").Select If Target.Address = "$C$11" Then MsgBox "Done." End Sub This causes an "ambiguous error" -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292 View this thread: http://www.excelforum.com/showthread...hreadid=475575 |
#5
|
|||
|
|||
SOOO CLOSE, PLEASE HELP!!!!!!! Cause of your help, it makes me look good to the boss, Thank you all VERY VERY much. -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292 View this thread: http://www.excelforum.com/showthread...hreadid=475575 |
#6
|
|||
|
|||
You get one Worksheet_change per worksheet. So the code has to be combined into
one procedure. Instead of putting lines of code that checks each address, you could create an array of those addresses and then use that array to find out where you are and where you should be next. This has some changes in it from the original code. If you empty a cell (hit the delete key), you stay in that cell instead of advancing to the next. When you finish, it goes back to the first cell in that list of addresses. If you change a cell not part of the list, you go to the first cell in the list. Make sure you keep that "option Base 1" at the top of the module. It's important. Option Explicit Option Base 1 Private Sub Worksheet_Change(ByVal Target As Range) Dim myAddr As Variant Dim res As Variant If Target.Cells.Count 1 Then Exit Sub If IsEmpty(Target) Then Exit Sub myAddr = Array("B1", "C2", "C3", "A6", "B6", "C6", "D6", "E6", "C11") If Target.Address = "$B$1" Then On Error Resume Next Target.Parent.Name = Target.Value If Err.Number < 0 Then MsgBox "Error with renaming of sheet!" Err.Clear End If On Error GoTo 0 End If res = Application.Match(Target.Address(0, 0), myAddr, 0) On Error GoTo errHandler: Application.EnableEvents = False If IsError(res) Then 'not in one of those cells, what should happen Me.Range(myAddr(1)).Select Else If res = UBound(myAddr) Then MsgBox "Done." 'or select the first cell to start over 'or just comment out the next line to stay put. Me.Range(myAddr(1)).Select Else Me.Range(myAddr(res + 1)).Select End If End If errHandler: Application.EnableEvents = True End Sub comotoman wrote: I have this titlle now for a different code, does it have to use this? Or can it be changed. Thus far I can not get it to work:( Private Sub Worksheet_Change(ByVal Target As Range) My code 'Template (Sheet 1)' which is my third sheet in workbook. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Target.Parent.Name = Target.Value End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$1" Then Range("C2").Select If Target.Address = "$C$2" Then Range("C3").Select If Target.Address = "$C$3" Then Range("A6").Select If Target.Address = "$A$6" Then Range("B6").Select If Target.Address = "$B$6" Then Range("C6").Select If Target.Address = "$C$6" Then Range("D6").Select If Target.Address = "$D$6" Then Range("E6").Select If Target.Address = "$E$6" Then Range("C11").Select If Target.Address = "$C$11" Then MsgBox "Done." End Sub This causes an "ambiguous error" -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php...o&userid=27292 View this thread: http://www.excelforum.com/showthread...hreadid=475575 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro help - copy a cell down | Excel Discussion (Misc queries) | |||
Create a print macro that would automatically select print area? | Excel Worksheet Functions | |||
Macro Help | Excel Worksheet Functions | |||
How do I pause a macro to select specific cells | Excel Worksheet Functions | |||
select worksheet to run macro | Excel Discussion (Misc queries) |