Solved: How to enable/disable Controls based on ActiveSheet.Name

[smecham]

What I am trying to do is only enable the controls on a UserForm that are relevant to the particular sheet that is being viewed.

I have no problem enabling the controls what I need is to be able to generate a list of all sheet names in the workbook and if the Sheet Name has a Given text "1234" in the Name then the UserForm Enables the Controls.

I need this to prevent the user from using the wrong controls for the wrong sheets....

Thanks in advance for any advice...

[Norie]

Are you sure you need a list of names?

Can't you just check the name of the active sheet using something like InStr?

If Instr(ActiveSheet.Name, "1234") Then
Msgbox "Active sheet is called something with 1234 in it"
Else
Msgbox "Active sheet isn't called something with 1234 in it"
End If

[XLGibbs]

You can also manipulate the enabled properties of certain functions if they are only sheet specific...

When a particular sheet is selected for example you could just have conditionals or case select mechanism for which functions are enabled on the form...

If ActiveSheet.Name <> "1234" then Commandbutton1.Enabled = False

This may require certain code to account for sheet selectionchanges to trigger a reload of the form in some way...but there are options out there...if you want to elaborate...

[smecham]

Thanx Norie & Gibbs I hadn't thought of either of those.... I guess I was making it more difficult than it needed to be.... That's why it is nice to have forums like this....

I will try both of these suggestions and let you know how it turns out...

[smecham]

I have tried both methods and the the best siuted for the application was just using the InStr to find the Part of the Sheet Name I was looking for.

Mainly because the Form I am using is vbModeless....

Here is waht I ended up with:

If InStr(ActiveSheet.Name, "POTS") Then
MsgBox "Using a POTS Work Sheet"
DisableDSLButtons
Else
If InStr(ActiveSheet.Name, "DSL") Then
MsgBox "Using a DSL Work Sheet"
DisablePOTSButtons
Else
MsgBox "Can't Determine Work Sheet Type"
Call DisablePOTSButtons
Call DisableDSLButtons
End If
End If