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

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

Sorry, left a line out:

Private Sub Workbook_Open()
Sheets("Sheet1").Select
Range(A2).Select
End Sub

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

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

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