Enabling/Disabling ActiveX Command Buttons
Hi Team
I have seen many variants (which do not seem to work) of this.
Essentially, I have an Admin Control sheet that determines what users can access and what they can, or cannot interact with.
I am trying to disable specific cmdBtn's so that the user can only use the enabled buttons.
Any assistance is appreciated.
TIA
Mark.
This is what I have thus far:
Sub CheckUser()
Dim sSheet As Worksheet
Dim myOb As Object
Dim uRow, sCol As Long
Dim dSheet, sName As String
Set sSheet = Sheets("Admin")
dSheet = "Dashboard"
With sSheet
.Calculate
If .Range("B8").Value = Empty Then
MsgBox "UserName not Registered, Please contact [The Administrator] for access rights"
Exit Sub
End If
If .Range("B7").Value < True Then
MsgBox "Incorrect Password! Please Try Again"
Exit Sub
End If
uRow = .Range("$B$8").Value
For sCol = 8 To 21
sName = .Cells(4, sCol).Value
If .Cells(uRow, sCol).Value = "Ð" Then
Sheets(sName).Unprotect "TooBadSoSad"
Sheets(sName).Visible = xlSheetVisible
If Sheets(sName) = dSheet Then
With Sheets(dSheet)
For Each myOb In dSheet
Select Case myOb
Case Is = "cmdBtn_Import"
.Enabled = True
Case Is = "cmdBtn_Goto_Register"
.Enabled = True
Case Is = "cmdBtn_goto_Status"
.Enabled = True
Case Is = "cmdBtn_Goto_Bulk"
.Enabled = True
Case Is = "cmdBtn_Goto_PAG"
.Enabled = True
Case Is = "cmdBtn_Goto_NSW"
.Enabled = True
Case Is = "cmdBtn_Goto_QLD"
.Enabled = True
Case Is = "cmdBtn_Goto_SA"
.Enabled = True
Case Is = "cmdBtn_Goto_VIC"
.Enabled = True
Case Is = "cmdBtn_Goto_BKPI"
.Enabled = True
Case Is = "cmdBtn_Goto_PKPI"
.Enabled = True
Case Is = "cmdBtn_View_BKPI"
.Enabled = True
Case Is = "cmdBtn_View_PKPI"
.Enabled = True
Case Is = "cmdBtn_Print_BKPI"
.Enabled = True
Case Is = "cmdBtn_Print_PKPI"
.Enabled = True
End Select
Next myOb
End With
End If
End If
If .Cells(uRow, sCol).Value = "Ï" Then
Sheets(sName).Protect "TooBadSoSad"
Sheets(sName).Visible = xlSheetVisible
If Sheets(sName) = dSheet Then
With Sheets(dSheet)
For Each myOb In dSheet
Select Case myOb
Case Is = "cmdBtn_Import"
.Enabled = False
Case Is = "cmdBtn_Goto_Register"
.Enabled = False
Case Is = "cmdBtn_goto_Status"
.Enabled = False
Case Is = "cmdBtn_Goto_Bulk"
.Enabled = False
Case Is = "cmdBtn_Goto_PAG"
.Enabled = False
Case Is = "cmdBtn_Goto_NSW"
.Enabled = False
Case Is = "cmdBtn_Goto_QLD"
.Enabled = False
Case Is = "cmdBtn_Goto_SA"
.Enabled = False
Case Is = "cmdBtn_Goto_VIC"
.Enabled = False
Case Is = "cmdBtn_Goto_BKPI"
.Enabled = True
Case Is = "cmdBtn_Goto_PKPI"
.Enabled = True
Case Is = "cmdBtn_View_BKPI"
.Enabled = True
Case Is = "cmdBtn_View_PKPI"
.Enabled = True
Case Is = "cmdBtn_Print_BKPI"
.Enabled = True
Case Is = "cmdBtn_Print_PKPI"
.Enabled = True
End Select
Next myOb
End With
End If
End If
If .Cells(uRow, sCol).Value = "x" Then Sheets(sName).Visible = xlVeryHidden
Next sCol
End With
End Sub
|