PC and Mac compatible file

[MickJacko]

Hi,

I have a small excel file with several macros.
Works fine on a pc, but go to a mac and it does not function.

Anyone got any ideas OR can look at it for me?

 

[mikerickson]

What kind of problems are you encountering?
Filepath issues can be fixed by using Application.PathSeparator instead of "\", but calls to API functions and use of the Dictionary object require the use of conditional compilation.

Also, what kind of "doesn't function"? Errror messages or erroneous results?

 

[MickJacko]

Thanks Mike,

We do not get any error messages.
It is for a rugby club which I help to manage.
We create reports based on hiding columns.
The hiding of columns is done in an "Admin tab" where we select what we want to see and then go to the "Report tab" and only the select fields are shown.

Works perfect on a pc, but users on a mac cant run this function.

The VB looks like (which on selection of a checkbox, hides a column in another tab):

Private Sub CheckBox1_Click()
Application.ScreenUpdating = False
If CheckBox1.Value = True Then
Sheet4.Select
Sheet4.Columns("B:B").Select
Selection.EntireColumn.Hidden = False
ElseIf CheckBox1.Value = False Then
Sheet4.Select
Sheet4.Columns("B:B").Select
Selection.EntireColumn.Hidden = True
End If
Sheet3.Select
Application.ScreenUpdating = True


End Sub


Private Sub CheckBox2_Click()
Application.ScreenUpdating = False
If CheckBox2.Value = True Then
Sheet4.Select
Sheet4.Columns("C:C").Select
Selection.EntireColumn.Hidden = False
ElseIf CheckBox2.Value = False Then
Sheet4.Select
Sheet4.Columns("C:C").Select
Selection.EntireColumn.Hidden = True
End If
Sheet3.Select
Application.ScreenUpdating = True
End Sub


Any ideas?

 

[mikerickson]

It looks like you are using ActiveX controls. Mac doesn't support ActiveX.
You should be able to translate that into using Forms controls.

 

[mikerickson]

If you create a Forms checkbox, named Check Box 1 (note the spaces) and assign it to this sub, it should do the trick. (that's one check box down, N to go. )

Note the use of .ControlFormat and that Value = xlOn (or xlOff) rather than True (or False).

Public Sub CheckBox1Routine()
    Application.ScreenUpdating = False
    If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = xlOn Then
        Sheet4.Select
        Sheet4.Columns("B:B").Select
        Selection.EntireColumn.Hidden = False
    Else
        Sheet4.Select
        Sheet4.Columns("B:B").Select
        Selection.EntireColumn.Hidden = True
    End If
    Sheet3.Select
    Application.ScreenUpdating = True
End Sub

The unesseary selection can also be removed

Public Sub CheckBox1Routine()
    Application.ScreenUpdating = False
    If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = xlOn Then
        Sheet4.Columns("B:B").EntireColumn.Hidden = False
    Else
        Sheet4.Columns("B:B").EntireColumn.Hidden = True
    End If
    Sheet3.Select
    Application.ScreenUpdating = True
End Sub

or

Public Sub CheckBox1Routine()
    Application.ScreenUpdating = False
   
    Sheet4.Columns("B:B").EntireColumn.Hidden = (ActiveSheet.Shapes("Check Box 1").ControlFormat.Value <> xlOn)
    
    Sheet3.Select
    Application.ScreenUpdating = True
End Sub

 

[MickJacko]

Great - I will give this a shot..!

Thank you for your time.

And N = alot

 

[mikerickson]

Consider assigning all the checkboxes to a single routine like:

Sub AllCheckBox()
Dim ColToHide as Range

With Sheet4
    Select Case Application.Caller
        Case "Check Box 1"
            Set ColToHide = .Range("B:B")
        Case "Check Box 2"
            Set ColToHide = .Range("C:C")
        Case "Check Box 3"
            Set ColToHide = .Range("D:D")
    End Select
End With

ColToHide.EntireColumn.Hidden = (ActiveSheet.Shapes(Application.Caller).ControlFormat.Value <> xlOn)
End Sub

or (if the name/column correspondence is always regular)

Sub AllCheckBox2()
    Dim ColToHide as Range
    Dim BoxIndex as Long

    BoxIndex = Val(Right(Application.Caller, 2))

    Set ColToHide = Sheet4.Columns(BoxIndex + 1)

    ColToHide.EntireColumn.Hidden = (ActiveSheet.Shapes(Application.Caller).ControlFormat.Value <> xlOn)
End Sub

 

[MickJacko]

Hi,

I have tried to make this work with no luck.
Copied code above and lose all functionality - I am doing something wrong.

Any advice OR any interest in doing this for me?