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?
[vba]
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[/vba]

[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:

[VBA] 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
[/VBA]