Conditionally Hide or deactivate code in an activeX command Button.

Hi Kramxel,

Try the attached workbook (tested using Excel 2003). Please note that I have had problems in the past moving ActiveX controls between Excel Versions. If the sample workbook doesn't work, please delete the Active X Buttons and replace them with new Active X 'CommandButton1' and 'CommandButton2' before giving up and thinking that the code doesn't work.

ThisWorkbook Code:

Private Sub Workbook_Open()

  'The CommandButtons are disabled/hidden by the 'Worksheet_Activate()' command, which is triggered when
  'focus moves to a DIFFERENT Sheet.  'Worksheet_Activate()' is NOT TRIGGERED on the first sheet the
  'Workbook is open to.
  
  'Set the focus on the LAST SHEET
  Sheets(Sheets.Count).Select
  
  'Set the focus on the FIRST SHEET to trigger 'Worksheet_Activate()'
  Sheets(1).Select
End Sub

Sheet Code (on every sheet):

Option Explicit

Private Sub CommandButton1_Click()
  MsgBox "Active X CommandButton1 has been clicked on Sheet '" & ActiveSheet.Name & "'."
End Sub

Private Sub CommandButton2_Click()
  MsgBox "Active X CommandButton2 has been clicked on Sheet '" & ActiveSheet.Name & "'."
End Sub

Private Sub Worksheet_Activate()
  Call EnableDisplayOrHideActiveXCommandButtonsOnActiveSheet
End Sub

Ordinary Module Code (e.g. Module1):

Option Explicit

Sub EnableActiveXCommandButtonsOnActiveSheet()
  'This is used in software development to display and activate Active X CommandButtons

  ActiveSheet.OLEObjects("CommandButton1").Visible = True
  ActiveSheet.OLEObjects("CommandButton1").Enabled = True
  ActiveSheet.OLEObjects("CommandButton2").Visible = True
  ActiveSheet.OLEObjects("CommandButton2").Enabled = True

End Sub


Sub EnableDisplayOrHideActiveXCommandButtonsOnActiveSheet()
  'This disables or hides CommandButtons when the date on the Sheet Name is more than 7 days away from today's date
  'Sheet Name format wcdd.mm.yy
  '
  'e.g. if date on Sheet is September 1, then the CommandButtons will be disabled/hidden on or after September 9
  
  Dim mySheetDate As Date

  Dim iDayOfMonth As Integer
  Dim iMonth As Integer
  Dim iYear As Integer
  
  Dim iDeltaDays As Long

  Dim sDayOfMonth As String
  Dim sMonth As String
  Dim sSheetName As String
  Dim sYear As String
  
  'Get the Name of the Active Sheet
  sSheetName = ActiveSheet.Name
  
  'Exit if the name of the Sheet is too short or too long
  If Len(sSheetName) <> Len("wcdd.mm.yy") Then
    Exit Sub
  End If
  
  '''''''''''''''''''''''''''''''''''''''
  'Get the date from the sheet
  '''''''''''''''''''''''''''''''''''''''
  
  'Get the month, day, and year as strings
  sDayOfMonth = Mid(sSheetName, 3, 2)
  sMonth = Mid(sSheetName, 6, 2)
  sYear = Mid(sSheetName, 9, 2)
  
  'Exit if the month, day, or year is not a number
  If Not IsNumeric(sDayOfMonth) Then
    Exit Sub
  End If
  If Not IsNumeric(sMonth) Then
    Exit Sub
  End If
  If Not IsNumeric(sYear) Then
    Exit Sub
  End If
  
  'Convert the strings to numbers
  iDayOfMonth = Int(sDayOfMonth)
  iMonth = Int(sMonth)
  iYear = Int(sYear) + 2000
  
  'Create the Sheet Date
  mySheetDate = DateSerial(iYear, iMonth, iDayOfMonth)
  
  'Find the number of days between the Sheet Date and today
  iDeltaDays = Date - mySheetDate
  'Debug.Print "ideltadays = " & iDeltaDays
  
  '''''''''''''''''''''''''''''''''''''''
  'Process CommandButton1
  '''''''''''''''''''''''''''''''''''''''
  
  'Make CommandButton Visible
  'Enable CommandButton
  ActiveSheet.OLEObjects("CommandButton1").Visible = True
  ActiveSheet.OLEObjects("CommandButton1").Enabled = True

  
  'Disable or Hide the CommandButton when the number of days is greater than or equal to 8
  If iDeltaDays >= 8 Then
  
    'Disable CommandButton
    ActiveSheet.OLEObjects("CommandButton1").Enabled = False
    
  End If
  
  '''''''''''''''''''''''''''''''''''''''
  'Process CommandButton2
  '''''''''''''''''''''''''''''''''''''''
  
  'Make CommandButton Visible
  'Enable CommandButton
  ActiveSheet.OLEObjects("CommandButton2").Visible = True
  ActiveSheet.OLEObjects("CommandButton2").Enabled = True

  
  'Disable or Hide the CommandButton when the number of days is greater than or equal to 8
  If iDeltaDays >= 8 Then
  
    'Disable CommandButton
    ActiveSheet.OLEObjects("CommandButton2").Visible = False
    
  End If

End Sub

Lewis

It's a known feature (which I just discovered) that Excel occasionally behaves in the manner you described (Runtime Error 1004 for a select or activate) during 'Workbook_Open().

Try the attached file which implements the following changes:

ThisWorkbook Module:

Private Sub Workbook_Open()

  'The CommandButtons are disabled/hidden by the 'Worksheet_Activate()' command, which is triggered when
  'focus moves to a DIFFERENT Sheet.  'Worksheet_Activate()' is NOT TRIGGERED on the first sheet the
  'Workbook is open to.
  
  'The following code was moved to an ordinary code module to prevent Runtime Error 1004 at 'Workbook_Open()'
  'Set the focus on the LAST SHEET
  'Sheets(Sheets.Count).Select
  
  'Set the focus on the FIRST SHEET to trigger 'Worksheet_Activate()'
  'Sheets(1).Select
  
  Application.OnTime Now(), "RealWorkbookOpenEvent"
  
End Sub

Ordinary Code Module (the following was added):

Sub RealWorkbookOpenEvent()
  'This is used to prevent Runtime Error 1004 in 'Workbook_Open()'
  
  'The CommandButtons are disabled/hidden by the 'Worksheet_Activate()' command, which is triggered when
  'focus moves to a DIFFERENT Sheet.  'Worksheet_Activate()' is NOT TRIGGERED on the first sheet the
  'Workbook is open to.
  
  'Set the focus on the LAST SHEET
  Sheets(Sheets.Count).Select
  
  'Set the focus on the FIRST SHEET to trigger 'Worksheet_Activate()'
  Sheets(1).Select
  
End Sub

Lewis

Bizarre this didn't solve the problem either exact same problem (but when I call the sheet by name works fine)

Thanks for the feedback. I'll make a note that the problem still exists for future reference.