Shortcut to "Form"

[cordeaux]

I am new to excel. I am trying to start up a sheet with a box to indicate whether you want to input a new record this would then take you straight to the Form box if needed. Can this be done? If so how?

 

[Audiojoe]

Yeah sure, put this in your Worksheet Activate event:

response = MsgBox("Do you wish to input data?", vbYesNo + vbQuestion, "Daddy Form!")
If response = vbno Then
Exit Sub
else
userform1.show
end sub

Audiojoe
I need a little break to get away for a holiday. So I can see the sun, cos in the sun they say it's fun, if you get some

 

[Audiojoe]

Sorry, that wasn't very clear. OK, right click on the sheet tab of the sheet concerned and select VIEW CODE.

Now above the big blank white bit there are two drop down boxes. Select WORKSHEET from the left hand one, and ACTIVATE from the right hand one. Now paste the code in there

Audiojoe

 

[cordeaux]

Thanks audiojoe, I,ve tried to do as you stated, but I can't get it to work. I copied and pasted your code into the worksheet activate window (the bit above the line)is that right? I then saved it came out and opened the sheet afresh and nothing happened. Any ideas? I,ve just got 2 records there at the moment but if I use the toolbar it still shows the Form. Sorry to be so dippy!

 

[Audiojoe]

Sorry, my bad. SHould have had an End If in there. Right the code should look exactly like this:

Private Sub Worksheet_Activate()
response = MsgBox("Do you wish to input data?", vbYesNo + vbQuestion, "Daddy Form!")
If response = vbNo Then
Exit Sub
Else
UserForm1.Show
End If
End Sub

Audiojoe

 

[cordeaux]

Sorry still not working. I,ve copied what i've got in the WORKSHEET ACTIVATE window but every time i save and restart the sheet i still get the normal sheet view. I appreciate the help, any more ideas?


Private Sub Worksheet_Activate()
response = MsgBox("Do you wish to input data?", vbYesNo + vbQuestion, "Daddy Form!")
If response = vbNo Then
Exit Sub
Else
UserForm1.Show
End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

 

[Audiojoe]

Try clicking onto another sheet and then back onto it

 

[cordeaux]

Thats got the daddy form box to appear, but there is a run time error "424" object required and when i go to debug its the line

UserForm1.Show

 

[Audiojoe]

What is your form called? Userform 1 is the standard name for the first userform you have created, but if you have changed the name to say JumpingJack then you need to change that line to:

JumpingJack.show

Audiojoe

 

[cordeaux]

Sorry about being so stupid. The form defaulted to being called "trial", in "trial" (ie sheet1)of a file "book1". I've tried putting in the line

trial.Show

but this comes up with the same error as before.