Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 6th 20, 04:00 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 37
Default 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   Report Post  
Old November 6th 20, 04:17 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,812
Default 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   Report Post  
Old November 9th 20, 02:09 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 37
Default 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   Report Post  
Old November 9th 20, 11:00 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,812
Default 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   Report Post  
Old November 10th 20, 07:02 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2020
Posts: 37
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditionally disabling activex toggle buttons The Researcher Excel Programming 1 February 28th 10 03:17 PM
Enabling/Disabling multiple option buttons Will Excel Programming 4 January 17th 09 08:49 PM
Command Buttons & ActiveX Controls aussiegirlone Excel Discussion (Misc queries) 4 December 17th 08 02:56 PM
Command Buttons & ActiveX Controls aussiegirlone Excel Discussion (Misc queries) 2 December 16th 08 02:26 PM
Userforms: Disabling - Enabling the X Lauri Excel Programming 4 May 12th 06 07:02 PM


All times are GMT +1. The time now is 09:24 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017