Show userform when cell is selected

[buzz71023]

I need to be able to make a macro run to show a userform when a cell is selected. Hopefully this will fix the solution to my problem. I dont want an user to be able to enter data into this cell unless its through the useform. The cells defined name is "LotNumber".

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Name.Name = "LotNumber" Then UserForm1.Show
End Sub

 

[buzz71023]

as this sheet is copied and renamed will the code stay with the copied sheet as well? because a have a macro to copy this sheet and i need it to stay with the new copied and renamed sheet.

 

[VoG]

The code will travel with the sheet.

 

[buzz71023]

the code is not doing anything... I tried saving and going back in but it still didnt do anything and now i am getting a run-time error 1004, Application-defined or object-defined error everytime i select a cell that does not have a defined name. i've double checked my name and it is LotNumber.

 

[buzz71023]

if i wanted to add more defined names/cells to this sheet, would i just keep using the same code over and over with the appropriate names and userforms?

 

[VoG]

To avoid the error

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Target.Name.Name = "LotNumber" Then UserForm1.Show
On Error GoTo 0
End Sub

To work with more named ranges replicate the line in red.

 

[buzz71023]

ok the error is gone but the userform is still not showing when that cell is being selected. all i did was paste your code and claify that the names were correct (Userform and Cell defined name are both correct).

 

[VoG]

It works for me. The code has to go in the sheet's code module and macros must be enabled. This is a better version

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrHandl
If Target.Name.Name = "LotNumber" Then UserForm1.Show
Exit Sub
ErrHandl:
Err.Clear
End Sub

 

[buzz71023]

First, Thanks for your help and your patience

Yeah, I dont know why it is not working for me. Macros are enabled (I have a about a 100 or so one this one sheet that have been working for 6 months or so. I even tried another named cell and userform and still didnt work for me. do i need to hit the Run button in VBE and select a macro or anything like that because when i hit the Run button in VBE all the Macros pop up.

My exact steps:
In VBE I right clicked on "For Copy" (which is the name of my sheet) and then posted the code you gave me and changed the name of the Userform "LotNumber" (which is also the name of my userform. Close out VBE, Goto my spread sheet and try selecting the cell with the name LotNumber. Sound correct?

Does the version of excel matter?