Simple VBA/Macro not working on Mac

[pearsonhenry2]

A few months ago I made a relatively simple spreadsheet for a friend, used to record CPD sessions.
It ended up involving a simple form, but this used some VBA.

It was working back in February, but now has stopped working on mac. It now gives the error "Run-time error 1004 method vlookup of object 'WorksheetFunction' failed"

I have tried it on another friends mac, and tried turning on the developer toolbar etc. To be honest struggled to find my way around the mac interface to find approvals for macros.

Any ideas or suggestions gratefully received.

Private Sub Clinical_Change()

End Sub

Private Sub CategoryOfEvent_Change()

End Sub

Private Sub CommandButton1_Click()
'when we click continue button
'MsgBox "The UF will be closed", 0, "Message"

Dim TargetRow As Integer
TargetRow = Sheets("Engine").Range("C3").Value + 1
Sheets("Records").Range("Records_Start").Offset(TargetRow, 0).Value = TargetRow
Sheets("Records").Range("Records_Start").Offset(TargetRow, 1).Value = CDate(dt_date)
Sheets("Records").Range("Records_Start").Offset(TargetRow, 2).Value = Shift
Sheets("Records").Range("Records_Start").Offset(TargetRow, 3).Value = Application.WorksheetFunction.VLookup(Shift, Sheets("Reference_data").Range("B3:C8"), 2, False)
Sheets("Records").Range("Records_Start").Offset(TargetRow, 4).Value = CategoryOfEvent
Sheets("Records").Range("Records_Start").Offset(TargetRow, 5).Value = txt_Description
Sheets("Records").Range("Records_Start").Offset(TargetRow, 6).Value = txt_DevPoints
Sheets("Records").Range("Records_Start").Offset(TargetRow, 7).Value = txt_DevPlan
Sheets("Records").Range("Records_Start").Offset(TargetRow, 8).Value = CInt(TimeOnReflection)








Unload Data_UF
End Sub

Private Sub CommandButton2_Click()
Unload Data_UF
End Sub

Private Sub dt_Date_Change()

End Sub

Private Sub Label1_Click()

End Sub

Private Sub Label10_Click()

End Sub

Private Sub Label3_Click()

End Sub

Private Sub Label4_Click()

End Sub

Private Sub Label5_Click()

End Sub

Private Sub PaediatricNo_Click()

End Sub

Private Sub txt_notes_Change()

End Sub

Private Sub Label9_Click()

End Sub

Private Sub Shift_Change()

End Sub

Private Sub TimeOnReflection_Change()

End Sub

Private Sub txt_DevPlan_Change()

End Sub

Private Sub UserForm_Click()

End Sub

Here is a link to the file if it's helpful: CPD Record V2 blank.xlsm

 

[Dan_W]

Hi. Judging from the error message, I suspect the issue originates with the use for the VLOOKUP formula in VBA.

Assuming that there is a sheet called "Reference_data" in the workbook, against which the code tells VBA to run the VLOOKUP formula, the only issue that jumps out at me is that you haven't assigned a value to the Shift variable.

I guess you've edited the code to remove anything sensitive and that's perfectly understandable, but the code you've posted above/in the workbook you uploaded does not appear to assign any value to the Shift variable, thus throwing an error for the VLOOKUP formula. If the actual code has assigned a value to the variable, it may be that the assigned value is not something that can be used in a VLOOKUP formula. You can test this by setting out some test data on a worksheet and using the VLOOKUP to see what the output is. Basically, if the VLOOKUP returns errors in the worksheet, then the VBA equivalent will fail too.

 

[pearsonhenry2]

Hi @Dan_W
I think it was the VLOOKUP causing the error, but also when I opened it on a mac I got other errors (the drop downs in the form were not being populated).

The link to the dropbox is actually the whole, unedited, spreadsheet. No sensitive code, just main "Records" tab is empty
Shifts variable should be defined from what the user enters in the form (or selects from the drop down menu).


2nd screenshot is where the dropdowns get their lists, it uses Row Source. With a bit more reading it appears that mac doesn't like that. I'll do some digging but ideas welcome on easy alternatuves to rowsource?

 

[Dan_W]

I'm afraid I know nothing about Macs, but I heard that there was some issue with VBA and Userforms - I could just be making that up though. It doesn't seem like that would be the problem if one day it was working, and the next day it wasn't.

If you can't use RowSource, another way of adding items quickly is to use List - but you can't access that from the designer screen. You might want to try inserting the following code in the empty line before Dim TargetRow As Integer:

Dim ShiftRange As Range
Set ShiftRange = Sheets("Reference_data").Range("B3:B8")
Me.Shift.List = Application.WorksheetFunction.Transpose(ShiftRange.Value)

Ideally, this will read the data from Cells B3 to B8 and populate the combobox/dropdown control with it. That said, if this was working one day and not working the next, I can't help by feel that this will not solve the underlying problem. Let me know how it goes.

 

[Dan_W]

So I had a chance to try out you workbook. I had assumed that designated Shift range (cells B3:B8) were each populated with a shift option, but it seems that it includes empty cells and so the problem is likely one of the empty options in the dropdown control is being selected - which explains why VLOOKUP is returning an error. VLOOKUP cannot check for nothing. Looking at the rest of the code, it seems that you might encounter problems with the date entry and the time entry too - if the data is entered incorrectly, it will result in an error.

I did a quick rewrite of the code to now include data validation - so if the data being entered doesn't comply with what is required, it will just skip the entry altogether rather than break the program and show an error. It's up to you, but if you do decide to try it out, please make sure that you test it against test data and be sure to backup the key files before you do. What is set out below is all the code that is need in the userform, so you can replace it all.

Private Sub CommandButton1_Click()
    AddRecord
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Sub AddRecord()
    Dim TargetRow As Long, TargetShift As String, TargetShiftType As String, TargetDate As Long, TargetRange As Range
    
    TargetRow = Application.WorksheetFunction.CountA(Range("B7:B10042")) + 1
    If IsDate(dt_date.Text) Then TargetDate = CDate(dt_date.Text)                                                                                                ' Checks that data entered is in date format
    TargetShift = Shift.Value                                                                                                                                    ' Retrieves shift data
    If TargetShift <> vbNullString Then TargetShiftType = Application.WorksheetFunction.VLookup(Shift.Value, Sheets("Reference_data").Range("B3:C8"), 2, False)  ' Checks that shift data is not empty before undertaking VLOOKUP on shift type
    If IsNumeric(TimeOnReflection.Text) Then TargetTime = CInt(TimeOnReflection.Text) ' Checks that numerical data has been entered
    
    Set TargetRange = Application.ActiveWorkbook.Sheets("Records").Range("Records_Start").Offset(TargetRow).Resize(1, 9)
    TargetRange.Value = Array(TargetRow, TargetDate, TargetShift, TargetShiftType, CategoryOfEvent.Text, txt_Description.Text, txt_DevPoints.Text, txt_DevPlan.Text, TargetTime)
    
    Unload Me
End Sub

 

[pearsonhenry2]

@Dan_W
Thank you very much for that - amazing.

Just tried it on a mac and seems to be working for the user form.

The drop down lists (from row source) don't work but I'll try doing some reading to see what does work on mac for that.

 

[Dan_W]

That's really strange because your workbook / drop down control works perfectly for me. Did you try the alternative LIST property I mentioned above in Post#4?

 

[pearsonhenry2]

The dropdown works fine for me on Windows but not on the mac I just tried.
Will give the List property a go now and see how I get on with windows & mac.
Thank you.

 

[pearsonhenry2]

@Dan_W
The list solution also worked, thank you.
Just needed to put it down in the initialise section of the VBA.

Thank you for all your help, working again now.

Private Sub CategoryOfEvent_Click()

End Sub

Private Sub CommandButton1_Click()
    AddRecord
End Sub


Private Sub CommandButton2_Click()
    Unload Me
End Sub

Sub AddRecord()




     Dim TargetRow As Long, TargetShift As String, TargetShiftType As String, TargetDate As Long, TargetRange As Range
    
    TargetRow = Application.WorksheetFunction.CountA(Range("B7:B10042")) + 1
    If IsDate(dt_date.Text) Then TargetDate = CDate(dt_date.Text)                                                                                                ' Checks that data entered is in date format
    TargetShift = Shift.Value                                                                                                                                    ' Retrieves shift data
    If TargetShift <> vbNullString Then TargetShiftType = Application.WorksheetFunction.VLookup(Shift.Value, Sheets("Reference_data").Range("B3:C8"), 2, False)  ' Checks that shift data is not empty before undertaking VLOOKUP on shift type
    If IsNumeric(TimeOnReflection.Text) Then TargetTime = CInt(TimeOnReflection.Text) ' Checks that numerical data has been entered
    
    Set TargetRange = Application.ActiveWorkbook.Sheets("Records").Range("Records_Start").Offset(TargetRow).Resize(1, 9)
    TargetRange.Value = Array(TargetRow, TargetDate, TargetShift, TargetShiftType, CategoryOfEvent.Text, txt_Description.Text, txt_DevPoints.Text, txt_DevPlan.Text, TargetTime)
    
    Unload Me
End Sub

Private Sub Shift_Change()

End Sub

Private Sub txt_Description_Change()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
    Dim ShiftRange As Range
    Set ShiftRange = Sheets("Reference_data").Range("B3:B8")
    Me.Shift.List = Application.WorksheetFunction.Transpose(ShiftRange.Value)
    
    
        Dim CategoryRange As Range
    Set CategoryRange = Sheets("Reference_data").Range("E3:E8")
    Me.CategoryOfEvent.List = Application.WorksheetFunction.Transpose(CategoryRange.Value)
End Sub

 

[Dan_W]

That's excellent - thanks for letting me know.

Let me know if the revised code causes you any problems - I tried to make it so that it would, at least, stop breaking and showing error messages. If it all turns out ok, can I ask that you mark it as having be solved. It helps anyone else who might have a similar problem and saves the mods from having to chase people about it.