Printable View
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?
activex controls are not supported on the mac. posting the workbook would allow folks to help you more.
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
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.Quote:
Originally Posted by tpoynton
No, you cannot do anything with ActiveX controls on the Mac side.
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.