Simple VBA/Macro not working on Mac

pearsonhenry2

New Member
Joined
Jun 10, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

VBA Code:
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
 

Attachments

  • Screenshot 2021-06-10 224024.jpg
    Screenshot 2021-06-10 224024.jpg
    176.4 KB · Views: 7

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
789
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 10, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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?
 

Attachments

  • shift small.jpg
    shift small.jpg
    245.5 KB · Views: 3
  • row source.jpg
    row source.jpg
    247.3 KB · Views: 3

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
789
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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

Well-known Member
Joined
Jul 11, 2018
Messages
789
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

VBA Code:
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
 
Solution

pearsonhenry2

New Member
Joined
Jun 10, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
@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.
 

Attachments

  • Untitled 2.png
    Untitled 2.png
    183 KB · Views: 5
  • row source.jpg
    row source.jpg
    247.3 KB · Views: 5

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
789
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 10, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

New Member
Joined
Jun 10, 2021
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
@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.

VBA Code:
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

Well-known Member
Joined
Jul 11, 2018
Messages
789
Office Version
  1. 365
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,589
Messages
5,765,313
Members
425,272
Latest member
Umba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top