VBA in a sheet and not a module

[justinsy]

Hi, I am new to VBA, especially with excel mac. My vba's work fine in a module. However, a friend of mine recently sent be a worksheet with a vba attached to a single sheet. Firstly the buttons don't work and excel does not want to run the vba. It gives some message about not recognizing ActiveX buttons. I have tried assigning a new button but the vba still does not run. Any ideas?

[tpoynton]

activex controls are not supported on the mac. posting the workbook would allow folks to help you more.

[justinsy]

Thanks for your response. Here is the code.


Option Explicit


Private Sub CommandButton3_Click()
Dim bm As Range

Set bm = Selection
ApplyFilters
bm.Select
'Me.Range("A1").Select
End Sub

Private Sub ApplyFilters()
Dim r As Long
Dim c As Long
Dim ws As Worksheet
Dim TheFilterBits As Collection
Dim HeadingCol As Long

Set ws = Me

r = 3
HeadingCol = 6

On Error Resume Next
ws.ShowAllData
On Error GoTo 0

c = 1
Do While ws.Cells(HeadingCol, c) <> ""
If ws.Cells(r, c) <> "" Then
If Left(ws.Cells(r, c), 2) <> "//" Then
Set TheFilterBits = FilterBits(ws.Cells(r, c))
If TheFilterBits.Count = 1 Then
ws.Cells(HeadingCol, c).AutoFilter Field:=c, Criteria1:=TheFilterBits.Item(1)
Else ' must be 3
ws.Cells(HeadingCol, c).AutoFilter Field:=c, Criteria1:=TheFilterBits.Item(1), Operator:=TheFilterBits.Item(2), Criteria2:=TheFilterBits.Item(3)
End If
End If
End If

c = c + 1
Loop

End Sub

Private Function FilterBits(ByVal FilterString As String) As Collection
Dim l As Long

Set FilterBits = New Collection

FilterString = UCase(FilterString)

Select Case True
Case InStr(FilterString, " AND ") > 0
l = InStr(FilterString, " AND ")
FilterBits.Add Left(FilterString, l - 1)
FilterBits.Add 1
FilterBits.Add Mid(FilterString, l + 3 + 2)
Case InStr(FilterString, " OR ") > 0
l = InStr(FilterString, " OR ")
FilterBits.Add Left(FilterString, l - 1)
FilterBits.Add 2
FilterBits.Add Mid(FilterString, l + 2 + 2)
Case True
FilterBits.Add FilterString
End Select

End Function

Private Sub CommandButton2_Click()
Dim bm As Range

Set bm = Selection

On Error Resume Next
Me.ShowAllData
On Error GoTo 0

bm.Select

End Sub

[tpoynton]

...posting the workbook would allow folks to help you more.

not on my mac at the moment...deleting current buttons, replacing the them with buttons from the forms toolbar, and naming them as they are in your code should work. I'm not sure if you can delete activex controls on the mac side, though...that's why I thought the workbook would help.

[shades]

No, you cannot do anything with ActiveX controls on the Mac side.

[mikerickson]

What you could do is copy/paste that code into a normal module.
Then create a button from the Forms menu and assign the macro CommandButton3_Click to that button.

How to get the ActiveX controls off of the worksheet? I'm not sure, perhaps looping through the Shapes collection and deleting everything might catch the ActiveX controls.