Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enabling/Disabling ActiveX Command Buttons
Claus
A "HUGE" Man-hug heading your way. Works perfectly, thank you once again. Much appreciation Mark. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditionally disabling activex toggle buttons | Excel Programming | |||
Enabling/Disabling multiple option buttons | Excel Programming | |||
Command Buttons & ActiveX Controls | Excel Discussion (Misc queries) | |||
Command Buttons & ActiveX Controls | Excel Discussion (Misc queries) | |||
Userforms: Disabling - Enabling the X | Excel Programming |