Solved: Custom button only works with specificied sheet

[thomas.szwed]

Hi there,

I was wondering if anyone had some code to perform the operation of restricting which sheet my custom button on my custom toolbar will work with. I.E i only want one of my buttons (which runs a userform) to work on 'Sheet 1' and should the user be on 'Sheet 2' and click on the button then some sort of Msg Box appears instructing them to click on sheet 1 first. Is this possible at all?

Is it some sort of IF Statement??

Many Thanks for any help

[PaSha]

you can also run your userform when the user clicks on that second sheet and second button... just put there also a code in the command button ...

UserForm1.Show

and that's it

...
or you can also make so that if the user is on sheet 2 and clicks button 2 ... that the button Activates the first sheet for ex.

Sheets("first").Activate

...

there are many posibility...

but iif you don't use that second button on second sheet why don't u just delete him ??

[Bob Phillips]

Use the worksheet_activate event to load it, _deactivate to unload it.

[mikerickson]

If ActiveSheet.Name <> "Sheet1"
If MsgBox("The userform will only open on sheet 1." & vbcrlf _
"Do you want to activate Sheet 1 and the userform?", vbYesNo)= vbNo Then Exit Sub
ThisWorkbook.Sheets("Sheet1").Activate
End If
Userform1.Show

[thomas.szwed]

Thanku works a treat!