CODEWITHSUNDEEP
vbaactivexform-control
NU2this
NU2this

Reputation: 5

Triggering VBA code using Form Control Check Box instead of ActiveX

I have the code below working in a budget template for work. However, before I roll this out to all the users, I need to make it Mac friendly. Since Macs don't like ActiveX Controls, I'm trying to figure out a way to make this code work using form controls. Anyone have any ideas for me? Thanks!

Private Sub travelcalc_Click()
ActiveSheet.Unprotect Password:="PASSWORD"
    If Me.travelcalc.Value = False Then
    Range("C19:L20").Locked = False
    Sheets("Travel Calculator").Visible = False
Else
    Sheets("Travel Calculator").Visible = True
    Range("$C$19") = Worksheets("Travel Calculator").Range("$N$25")
    Range("$D$19") = Worksheets("Travel Calculator").Range("$P$25")
    Range("$E$19") = Worksheets("Travel Calculator").Range("$R$25")
    Range("$F$19") = Worksheets("Travel Calculator").Range("$T$25")
    Range("$G$19") = Worksheets("Travel Calculator").Range("$V$25")
    Range("$H$19") = Worksheets("Travel Calculator").Range("$X$25")
    Range("$I$19") = Worksheets("Travel Calculator").Range("$Z$25")
    Range("$J$19") = Worksheets("Travel Calculator").Range("$AB$25")
    Range("$K$19") = Worksheets("Travel Calculator").Range("$AD$25")
    Range("$L$19") = Worksheets("Travel Calculator").Range("$AF$25")
    Range("$C$20") = Worksheets("Travel Calculator").Range("$N$51")
    Range("$D$20") = Worksheets("Travel Calculator").Range("$P$51")
    Range("$E$20") = Worksheets("Travel Calculator").Range("$R$51")
    Range("$F$20") = Worksheets("Travel Calculator").Range("$T$51")
    Range("$G$20") = Worksheets("Travel Calculator").Range("$V$51")
    Range("$H$20") = Worksheets("Travel Calculator").Range("$X$51")
    Range("$I$20") = Worksheets("Travel Calculator").Range("$Z$51")
    Range("$J$20") = Worksheets("Travel Calculator").Range("$AB$51")
    Range("$K$20") = Worksheets("Travel Calculator").Range("$AD$51")
    Range("$L$20") = Worksheets("Travel Calculator").Range("$AF$51")
    Range("C19:L20").Locked = True
End If
ActiveSheet.Protect Password:="PASSWORD", AllowFormattingColumns:=True, AllowFormattingRows:=True

End Sub

Upvotes: 0

Views: 1057

Answers (2)

NU2this
NU2this

Reputation: 5

This macro assigned to the check box worked perfectly.

Sub CheckBox11_Click()
ActiveSheet.Unprotect Password:="PASSWORD"
    If ActiveSheet.Shapes("Check Box 11").OLEFormat.Object.Value <> 1 Then
    Range("C19:L20").Locked = False
        Sheets("Travel Calculator").Visible = False
Else
    Sheets("Travel Calculator").Visible = True
    Range("$C$19") = Worksheets("Travel Calculator").Range("$N$25")
    Range("$D$19") = Worksheets("Travel Calculator").Range("$P$25")
    Range("$E$19") = Worksheets("Travel Calculator").Range("$R$25")
    Range("$F$19") = Worksheets("Travel Calculator").Range("$T$25")
    Range("$G$19") = Worksheets("Travel Calculator").Range("$V$25")
    Range("$H$19") = Worksheets("Travel Calculator").Range("$X$25")
    Range("$I$19") = Worksheets("Travel Calculator").Range("$Z$25")
    Range("$J$19") = Worksheets("Travel Calculator").Range("$AB$25")
    Range("$K$19") = Worksheets("Travel Calculator").Range("$AD$25")
    Range("$L$19") = Worksheets("Travel Calculator").Range("$AF$25")
    Range("$C$20") = Worksheets("Travel Calculator").Range("$N$51")
    Range("$D$20") = Worksheets("Travel Calculator").Range("$P$51")
    Range("$E$20") = Worksheets("Travel Calculator").Range("$R$51")
    Range("$F$20") = Worksheets("Travel Calculator").Range("$T$51")
    Range("$G$20") = Worksheets("Travel Calculator").Range("$V$51")
    Range("$H$20") = Worksheets("Travel Calculator").Range("$X$51")
    Range("$I$20") = Worksheets("Travel Calculator").Range("$Z$51")
    Range("$J$20") = Worksheets("Travel Calculator").Range("$AB$51")
    Range("$K$20") = Worksheets("Travel Calculator").Range("$AD$51")
    Range("$L$20") = Worksheets("Travel Calculator").Range("$AF$51")
    Range("C19:L20").Locked = True
End If
ActiveSheet.Protect Password:="PASSWORD", AllowFormattingColumns:=True, AllowFormattingRows:=True

End Sub

Upvotes: 0

vacip
vacip

Reputation: 5416

After you insert the form control, right click it, and select Assign macro, and select New. A macro will be assigned to its "click event".

Checking if it has been checked is a bit tricky, but doable:

Private Sub CheckBox1_Click() 'assign this macro to your checkbox; make sure it is in a standard module
ActiveSheet.Unprotect Password:="PASSWORD"

If ActiveSheet.Shapes("Check Box 1").OLEFormat.Object.Value <> 1 Then
  Range("C19:L20").Locked = False
  Sheets("Travel Calculator").Visible = False
Else
  yadda-yadda your code here...
End if

ActiveSheet.Protect Password:="PASSWORD", AllowFormattingColumns:=True, AllowFormattingRows:=True
End Sub

To be honest, I can only hope this works on MAC, never tried that. But if you say Form controls work OK, then this should do it...

Upvotes: 1

Related Questions