Control button linked to Userform Excel for Mac

leopardhawk

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
611
Office Version
  1. 2016
Platform
  1. Windows
I have a very large workbook, years in the making, with lots of code, links to the Internet etc. After months of testing, I am confident that it works 100% perfectly in Excel for Windows on a Windows PC. Now, I have begun testing in Excel for Mac on an Mac PC and have found what I hope is a relatively small problem that someone here can help me with.

There are 13 command buttons on the Home worksheet and 11 of them work just fine (they link to other worksheets) but, there are two command buttons that both launch Userforms where the users will input their data. On the Mac, these two buttons do nothing, the cursor doesn't change to a 'hand' like it does with the other command buttons. Clicking either button just highlights the button.

If anyone can help point me in the right direction on this, I will be grateful. Thanks!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
If they are ActiveX buttons, you will need to change them to Form Control buttons, or shapes.
 
Upvote 0
@Fluff Thanks Fluff. The two buttons in question both launch a multi-form. How complicated will it be, changing them from ActiveX to Form Control buttons? I'm not even real sure what I would need to do. Also, if I do change them, can I assume they will still work in the Windows version of Excel?
 
Upvote 0
What code do you have behind those buttons?
 
Upvote 0
@Fluff This is the code for the 'Personal Info' button. I can also show you the code for the "Financial Info' button which is a bit more intense if you want to see it (it calls a multi-page userform with six tabs on it). Let me know. Thanks!

On another note, I know that there are a lot of people with Mac PCs and I don't really want to exclude them from all that this workbook has to offer. As much as I don't want to do it, I feel like if this is going to be too complicated, I will have to limit the workbook to only Windows users. I'm hoping you have a relatively easy solution.

VBA Code: 
Private Sub CloseCommandButton_Click()

Unload Me

End Sub

Private Sub ComboBox4_Change()

End Sub

Private Sub GenderComboBox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   With Me.GenderComboBox
      If .Text = "M" Or .Text = "F" Or .Text = "" Then
         .BackColor = rgbWhite
         Label4.Caption = "Gender"
         Label4.ForeColor = rgbBlack
      Else
         Label4.Caption = "Please select M or F"
         Label4.ForeColor = RGB(255, 55, 55)
         .BackColor = RGB(255, 55, 55)
         GenderComboBox.Value = ""
      End If
   End With
End Sub

Private Sub SGenderComboBox_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
   With Me.SGenderComboBox
      If .Text = "M" Or .Text = "F" Or .Text = "" Then
         .BackColor = rgbWhite
         Label24.Caption = "Gender"
         Label24.ForeColor = rgbBlack
      Else
         Label24.Caption = "Please select M or F"
         Label24.ForeColor = RGB(255, 55, 55)
         .BackColor = RGB(255, 55, 55)
         SGenderComboBox.Value = ""
      End If
   End With
End Sub

Private Sub UserForm_Initialize()
Dim objControl As MSForms.Control

    For Each objControl In Me.Controls
        If TypeName(objControl) = "TextBox" And objControl.Tag <> "" Then
           Me.setupPlaceholder objControl.Name, False
        End If
    Next objControl

    MultiPage1.Value = 0
    Me.FirstNameTextBox.SetFocus
        
'Fill all 'Month' combo boxes with valid months
Dim MonthAry(1 To 12) As Variant, ComboAry As Variant
    Dim i As Long
   
        ComboAry = Array("B", "R", "C", "O", "SB", "SR", "SC", "SO")
          For i = 1 To 12
        MonthAry(i) = MonthName(i)
    Next i
        For i = 0 To UBound(ComboAry)
        Me.Controls(ComboAry(i) & "Month").List = MonthAry
    Next i
    
'Fill all 'Day' combo boxes with valid days
Dim Arry As Variant
    
    Arry = Evaluate("if({1},row(1:31))")
        Me.BDay.List = Arry
        Me.RDay.List = Arry
        Me.CDay.List = Arry
        Me.ODay.List = Arry
        Me.SBDay.List = Arry
        Me.SRDay.List = Arry
        Me.SCDay.List = Arry
        Me.SODay.List = Arry
    
'Fill all 'Year' combo boxes with valid years
Dim Ary As Variant
    
    Ary = Evaluate("if({1},row(1930:2130))")
        Me.BYear.List = Ary
        Me.RYear.List = Ary
        Me.CYear.List = Ary
        Me.OYear.List = Ary
        Me.SBYear.List = Ary
        Me.SRYear.List = Ary
        Me.SCYear.List = Ary
        Me.SOYear.List = Ary
    
'Fill 'Gender' combo box with valid genders
    Me.GenderComboBox.List = Array("M", "F")
    Me.GenderComboBox.Style = fmStyleDropDownCombo
    
'Force users to choose from combo boxes
        Me.BMonth.Style = 2
        Me.BDay.Style = 2
        Me.BYear.Style = 2
        
        Me.SBMonth.Style = 2
        Me.SBDay.Style = 2
        Me.SBYear.Style = 2
        
        Me.RMonth.Style = 2
        Me.RDay.Style = 2
        Me.RYear.Style = 2
        
        Me.SRMonth.Style = 2
        Me.SRDay.Style = 2
        Me.SRYear.Style = 2
        
        Me.CMonth.Style = 2
        Me.CDay.Style = 2
        Me.CYear.Style = 2
        
        Me.SCMonth.Style = 2
        Me.SCDay.Style = 2
        Me.SCYear.Style = 2
        
        Me.OMonth.Style = 2
        Me.ODay.Style = 2
        Me.OYear.Style = 2
        
        Me.SOMonth.Style = 2
        Me.SODay.Style = 2
        Me.SOYear.Style = 2
    
'Fill 'Pension Provider' combo boxes with list of Canadian pension providers
    Dim LastRow As Long
    Dim SheetName As String
    SheetName = "Sheet20"
    LastRow = Sheets(SheetName).Cells(Rows.Count, "A").End(xlUp).Row
    Me.ProviderComboBox.List = Sheets("Sheet20").Range("A2:A" & LastRow).Value
    Me.SProviderComboBox.List = Sheets("Sheet20").Range("A2:A" & LastRow).Value
    
'Fill 'Pension Option' combo boxes with list of popular pension options
    Me.OptionComboBox.List = Array("100% Joint Life", _
        "100% Joint Life 5-year guarantee", "100% Joint Life 10-year guarantee", "100% Joint Life 15-year guarantee", _
        "75% Joint Life 5-year guarantee", "75% Joint Life 10-year guarantee", "75% Joint Life 15-year guarantee", _
        "60% Joint Life 5-year guarantee", "60% Joint Life 10-year guarantee", "60% Joint Life 15-year guarantee", _
        "Single Life", _
        "Single Life 5-year guarantee", "Single Life 10-year guarantee", "Single Life 15-year guarantee", _
        "Other")

'Fill 'Spouse/Partner Gender' combo box with valid genders
    Me.SGenderComboBox.List = Array("M", "F")
    Me.SGenderComboBox.Style = fmStyleDropDownCombo
    
'Fill 'Spouse/Partner Pension Option' combo boxes with list of popular pension options
    Me.SOptionComboBox.List = Array("100% Joint Life", _
        "100% Joint Life 5-year guarantee", "100% Joint Life 10-year guarantee", "100% Joint Life 15-year guarantee", _
        "75% Joint Life 5-year guarantee", "75% Joint Life 10-year guarantee", "75% Joint Life 15-year guarantee", _
        "60% Joint Life 5-year guarantee", "60% Joint Life 10-year guarantee", "60% Joint Life 15-year guarantee", _
        "Single Life", _
        "Single Life 5-year guarantee", "Single Life 10-year guarantee", "Single Life 15-year guarantee", _
        "Other")

End Sub

'Set focus to first available box on each tab of the multi-page userForm
Private Sub MultiPage1_Change()

    GetTextBoxByTabIndex(Me.MultiPage1.SelectedItem, 0).SetFocus

End Sub

Private Function GetTextBoxByTabIndex(ByVal ControlParent As Object, ByVal TabIndex As Long) As Control

    Dim oCtrl As Control
    
    For Each oCtrl In ControlParent.Controls
        If TypeOf oCtrl Is MSForms.TextBox Then
            If oCtrl.TabIndex = TabIndex Then
                Set GetTextBoxByTabIndex = oCtrl
                Exit For
            End If
        End If
    Next oCtrl

End Function

Private Sub OKCommandButton_Click()

'Combine month, day, year into valid dates
    Dim DOB As Variant
        On Error Resume Next
        DOB = DateSerial(Me.BYear, Month(DateValue(Me.BMonth & "/01/2020")), Me.BDay)
        On Error GoTo 0
    
    Dim RD As Variant
        On Error Resume Next
        RD = DateSerial(Me.RYear, Month(DateValue(Me.RMonth & "/01/2020")), Me.RDay)
        On Error GoTo 0
                   
    Dim CSD As Variant
        On Error Resume Next
        CSD = DateSerial(Me.CYear, Month(DateValue(Me.CMonth & "/01/2020")), Me.CDay)
        On Error GoTo 0
                   
    Dim OSD As Variant
        On Error Resume Next
        OSD = DateSerial(Me.OYear, Month(DateValue(Me.OMonth & "/01/2020")), Me.ODay)
        On Error GoTo 0
        
    Dim SDOB As Variant
        On Error Resume Next
        SDOB = DateSerial(Me.SBYear, Month(DateValue(Me.SBMonth & "/01/2020")), Me.SBDay)
        On Error GoTo 0
        
    Dim SRD As Variant
        On Error Resume Next
        SRD = DateSerial(Me.SRYear, Month(DateValue(Me.SRMonth & "/01/2020")), Me.SRDay)
        On Error GoTo 0
                   
    Dim SCSD As Variant
        On Error Resume Next
        SCSD = DateSerial(Me.SCYear, Month(DateValue(Me.SCMonth & "/01/2020")), Me.SCDay)
        On Error GoTo 0
                   
    Dim SOSD As Variant
        On Error Resume Next
        SOSD = DateSerial(Me.SOYear, Month(DateValue(Me.SOMonth & "/01/2020")), Me.SODay)
        On Error GoTo 0
                   
    With Sheets(11)

'Send data to sheets
        .Unprotect Password:="iluvcl0udy"
        
        If Not AllmostEmpty(FirstNameTextBox) Then .Range("C9").Value = FirstNameTextBox.Value
              
        If LastNameTextBox <> "Optional" Then
            If Not AllmostEmpty(LastNameTextBox) Then .Range("D9").Value = LastNameTextBox.Value
        End If
        
        If IsDate(DOB) Then .Range("E9").Value = DOB
        If Not AllmostEmpty(GenderComboBox) Then .Range("F9").Value = GenderComboBox.Value
        
        If CompanyTextBox <> "Optional" Then
            If Not AllmostEmpty(CompanyTextBox) Then .Range("G9").Value = CompanyTextBox.Value
        End If
        
        If IsDate(RD) Then .Range("C15").Value = RD
        If Not AllmostEmpty(OptionComboBox) Then .Range("D15").Value = OptionComboBox.Value
        If Not AllmostEmpty(ProviderComboBox) Then .Range("E15").Value = ProviderComboBox.Value
        If IsDate(CSD) Then .Range("F15").Value = CSD
        If IsDate(OSD) Then .Range("G15").Value = OSD
        
        .Protect Password:="iluvcl0udy"
    
    End With
        
    With Sheets(11)

        .Unprotect Password:="iluvcl0udy"
        
        If Not AllmostEmpty(SFirstNameTextBox) Then .Range("C11").Value = SFirstNameTextBox.Value
        
        If SLastNameTextBox <> "Optional" Then
            If Not AllmostEmpty(SLastNameTextBox) Then .Range("D11").Value = SLastNameTextBox.Value
        End If
        
        If IsDate(SDOB) Then .Range("E11").Value = SDOB
        If Not AllmostEmpty(SGenderComboBox) Then .Range("F11").Value = SGenderComboBox.Value
        
        If SCompanyTextBox <> "Optional" Then
            If Not AllmostEmpty(SCompanyTextBox) Then .Range("G11").Value = SCompanyTextBox.Value
        End If
        
        If IsDate(SRD) Then .Range("C17").Value = SRD
        If Not AllmostEmpty(SOptionComboBox) Then .Range("D17").Value = SOptionComboBox.Value
        If Not AllmostEmpty(SProviderComboBox) Then .Range("E17").Value = SProviderComboBox.Value
        If IsDate(SCSD) Then .Range("F17").Value = SCSD
        If IsDate(SOSD) Then .Range("G17").Value = SOSD
        
        .Protect Password:="iluvcl0udy"
    
    End With
    
Unload Me
    
End Sub

'Set textbox tags to gray
Sub setupPlaceholder(txtBox As String, focus As Boolean)
    With Me.Controls(txtBox)
        If Len(.Text) = 0 And Not focus Then
            .Text = .Tag
            .ForeColor = vbGrayText
        ElseIf .Text = .Tag Then
            .Text = ""
            .ForeColor = vbWindowText
        End If
    End With
End Sub

Private Sub LastNameTextBox_Enter()
    setupPlaceholder LastNameTextBox.Name, True
End Sub

Private Sub LastNameTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder LastNameTextBox.Name, False
End Sub

Private Sub CompanyTextBox_Enter()
    setupPlaceholder CompanyTextBox.Name, True
End Sub

Private Sub CompanyTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder CompanyTextBox.Name, False
End Sub

Private Sub SLastNameTextBox_Enter()
    setupPlaceholder SLastNameTextBox.Name, True
End Sub

Private Sub SLastNameTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder SLastNameTextBox.Name, False
End Sub

Private Sub SCompanyTextBox_Enter()
    setupPlaceholder SCompanyTextBox.Name, True
End Sub

Private Sub SCompanyTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    setupPlaceholder SCompanyTextBox.Name, False
End Sub

'Limit number of characters in each textbox or combobox
Private Sub ProviderComboBox_Change()
    If ProviderComboBox.TextLength > 42 Then
    MsgBox "Please limit your input to 42 characters."
    ProviderComboBox.Text = Left(ProviderComboBox, ProviderComboBox.TextLength - 1)
    End If
End Sub

Private Sub SProviderComboBox_Change()
    If SProviderComboBox.TextLength > 42 Then
    MsgBox "Please limit your input to 42 characters."
    SProviderComboBox.Text = Left(SProviderComboBox, SProviderComboBox.TextLength - 1)
    End If
End Sub

Private Sub OptionComboBox_Change()
    If OptionComboBox.TextLength > 34 Then
    MsgBox "Please limit your input to 34 characters."
    OptionComboBox.Text = Left(OptionComboBox, OptionComboBox.TextLength - 1)
    End If
End Sub

Private Sub SOptionComboBox_Change()
    If SOptionComboBox.TextLength > 34 Then
    MsgBox "Please limit your input to 34 characters."
    SOptionComboBox.Text = Left(SOptionComboBox, SOptionComboBox.TextLength - 1)
    End If
End Sub

Private Sub FirstNameTextBox_Change()
    If FirstNameTextBox.TextLength > 13 Then
    MsgBox "Please limit your input to 13 characters."
    FirstNameTextBox.Text = Left(FirstNameTextBox, FirstNameTextBox.TextLength - 1)
    End If
End Sub

Private Sub SFirstNameTextBox_Change()
    If SFirstNameTextBox.TextLength > 13 Then
    MsgBox "Please limit your input to 13 characters."
    SFirstNameTextBox.Text = Left(SFirstNameTextBox, SFirstNameTextBox.TextLength - 1)
    End If
End Sub

Private Sub LastNameTextBox_Change()
    If LastNameTextBox.TextLength > 28 Then
    MsgBox "Please limit your input to 28 characters."
    LastNameTextBox.Text = Left(LastNameTextBox, LastNameTextBox.TextLength - 1)
    End If
End Sub

Private Sub SLastNameTextBox_Change()
    If SLastNameTextBox.TextLength > 28 Then
    MsgBox "Please limit your input to 28 characters."
    SLastNameTextBox.Text = Left(SLastNameTextBox, SLastNameTextBox.TextLength - 1)
    End If
End Sub

Private Sub CompanyTextBox_Change()
    If CompanyTextBox.TextLength > 30 Then
    MsgBox "Please limit your input to 30 characters."
    CompanyTextBox.Text = Left(CompanyTextBox, CompanyTextBox.TextLength - 1)
    End If
End Sub

Private Sub SCompanyTextBox_Change()
    If SCompanyTextBox.TextLength > 30 Then
    MsgBox "Please limit your input to 30 characters."
    SCompanyTextBox.Text = Left(SCompanyTextBox, SCompanyTextBox.TextLength - 1)
    End If
End Sub
 
Upvote 0
That's th userform code, I meant the code behind the button that doesn't work.
 
Upvote 0
OMG, oops! Sorry about that. I hope that this is what you are asking for...

VBA Code: 
Sub SetAllScrollAreas()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    ws.ScrollArea = ws.UsedRange.Address

Next ws

End Sub
Private Sub CommandButton1_Click()

PersonalInfoUserForm.Show

End Sub
Private Sub CommandButton2_Click()

FinancialInfoUserForm.Show
    
End Sub
 
Upvote 0
Ok, thanks for that, just create a normal sub like
VBA Code: 
Sub abc()
   PersonalInfoUserForm.Show
End Sub
and then assign that macro to the button. & the same for the other button.
 
Upvote 0
@Fluff ok, thanks...

some questions from a noob...

1. am I replacing the 'private subs' that are already there with these 'normal' subs?

2. what is the significance of 'abc'? Do I use 'abc' for both buttons?

3. when you say 'assign that macro to the button', what macro and how do I assign it?

Sorry to be a pain.
 
Upvote 0
1) Yes
2) There is no significance, it's just the name of the sub & you can call it anything you want. You will need to create 2 subs with different names.
3) Right click the form control button/shape & select "Assign macro" & then select the relevant macro.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question
Excel for Mac 2021 v16.81. Send bulk email with an attachmen
Replies
2
Views
270
vk2023
V
S
  • Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
188
myall_blues
myall_blues
Geremias Fontes
  • Question
Creating a Create UserForm Add In for Excel for Mac OS
Replies
4
Views
1K
iantech
I
K
  • Solved
populating a textbox on a userform from selections that are made from a listbox that is one another userform (popup)...
Replies
4
Views
190
kbishop94
K
StephenC101
  • Question
Mac Excel macro help needed please
Replies
1
Views
283
jkpieterse
jkpieterse

Forum statistics

Threads
1,214,784
Messages
6,121,538
Members
449,038
Latest member
Guest1337

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top