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

Enabling/Disabling ActiveX Command Buttons
 

Hi Mark,

Am Thu, 5 Nov 2020 19:00:24 -0800 (PST) schrieb NoodNutt:

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.

try this to enable the buttons:

Sub CheckUser()
Dim ctrl As OLEObject
Dim dSheet As String

dSheet = "Dashboard"

With Worksheets(dSheet)
For Each ctrl In .OLEObjects
Select Case ctrl.Name
Case "cmdBtn_Import"
.Enabled = True
Case "cmdBtn_Goto_Register"
.Enabled = True
Case "cmdBtn_goto_Status"
.Enabled = True
Case "cmdBtn_Goto_Bulk"
.Enabled = True
Case "cmdBtn_Goto_PAG"
.Enabled = True
Case "cmdBtn_Goto_NSW"
.Enabled = True
Case "cmdBtn_Goto_QLD"
.Enabled = True
Case "cmdBtn_Goto_SA"
.Enabled = True
Case "cmdBtn_Goto_VIC"
.Enabled = True
Case "cmdBtn_Goto_BKPI"
.Enabled = True
Case "cmdBtn_Goto_PKPI"
.Enabled = True
Case "cmdBtn_View_BKPI"
.Enabled = True
Case "cmdBtn_View_PKPI"
.Enabled = True
Case "cmdBtn_Print_BKPI"
.Enabled = True
Case "cmdBtn_Print_PKPI"
.Enabled = True
End Select
Next
End With
End Sub

Insert the code into your existing code for checking the user.


Regards
Claus B.
--
Windows10
Office 2016

Enabling/Disabling ActiveX Command Buttons
 

Hi Claus

As always, thank you for your value input.

Unfortunately, this is throwing an (Error 438 - "Object doesn't support this Property or Method")

Apologies, Reading my OP, I probably should have stated that the cmdBtn's are ActiveX Control Objects.

Cheers
Mark.

Enabling/Disabling ActiveX Command Buttons
 

Hi Mark,

Am Sun, 8 Nov 2020 17:09:22 -0800 (PST) schrieb NoodNutt:

Unfortunately, this is throwing an (Error 438 - "Object doesn't support this Property or Method")

try:

Sub CheckUser()
Dim obj As OLEObject
Dim dSheet As String

dSheet = "Dashboard"

With Worksheets(dSheet)
For Each obj In .OLEObjects
Select Case obj.Name
Case "cmdBtn_Import"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_Goto_Register"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_goto_Status"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_Goto_Bulk"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_Goto_PAG"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_Goto_NSW"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_Goto_QLD"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_Goto_SA"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_Goto_VIC"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_Goto_BKPI"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_Goto_PKPI"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_View_BKPI"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_View_PKPI"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_Print_BKPI"
.OLEObjects(obj.Name).Enabled = True
Case "cmdBtn_Print_PKPI"
.OLEObjects(obj.Name).Enabled = True
End Select
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Enabling/Disabling ActiveX Command Buttons
 

Claus

A "HUGE" Man-hug heading your way.

Works perfectly, thank you once again.

Much appreciation
Mark.