locked
Excel VBA form doesn't work in Excel for Mac RRS feed

  • Question

  • Hi.

    Before I post the problem I want to clarify that I've been googling this for while now and can't seem to find to right way to solve it. Some people/pages say that it just "should" work, other point me to the Ron de Bruin webpage but I don't understand how to implement the correct code. 

    I have created an Excel-workbook with a VBA form in it for saving information in a new sheet every time you use the form. It works brilliant on my PC, but on my friends Mac it doesn't work at all. 

    I'll add the code here and if there's anyone out there with a good answer please let me know how to make this work on a Mac. I'm using Office 2013 and my friend is currently on a Mac with Office for Mac 2008. 

    Private Sub TextBox1_Change()
    
    End Sub
    
    Private Sub ClearButton_Click()
    
    Call UserForm_Initialize
    
    End Sub
    
    Private Sub OKButton_Click()
    
    Dim emptyRow As Long
    
    
    'copy sheet named "grundmall" and give it the name provided in fastighetsbeteckning 
        Sheets("grundmall").Copy Before:=Sheets(2)
        ActiveSheet.Name = fastighetsbeteckning.Value
    
    
    'Determine emptyRow
    With Sheets(fastighetsbeteckning.Value)
    emptyRow = WorksheetFunction.CountA(.Range("A:A")) + 1
    
    'Transfer information
    .Cells(emptyRow, 1).Value = fastighetsbeteckning.Value
    .Cells(emptyRow, 2).Value = projekttyp.Value
    .Cells(emptyRow, 3).Value = gatuadress.Value
    .Cells(emptyRow, 4).Value = ort.Value
    .Cells(emptyRow, 5).Value = loaboabta.Value
    .Cells(emptyRow, 6).Value = byggratt.Value
    .Cells(emptyRow, 7).Value = markyta.Value
    .Cells(emptyRow, 8).Value = planstatus.Value
    .Cells(emptyRow, 9).Value = bestallare.Value
    .Cells(emptyRow, 10).Value = saljare.Value
    .Cells(emptyRow, 11).Value = kontaktperson.Value
    .Cells(emptyRow, 12).Value = kopeskilling.Value
    .Cells(emptyRow, 13).Value = projektomkostnad.Value
    .Cells(emptyRow, 14).Value = byggstart.Value
    .Cells(emptyRow, 15).Value = ansvarig.Value
    .Cells(emptyRow, 16).Value = anm.Value
    
    End With
    ActiveWorkbook.Save 'save the entire workbook
    Call UserForm_Initialize
    
    End Sub
    
    Private Sub UserForm_Click()
    
    End Sub
    
    Private Sub UserForm_Initialize()
    
    fastighetsbeteckning.Value = ""
    
    projekttyp.Value = ""
    
    gatuadress.Value = ""
    
    ort.Value = ""
    
    loaboabta.Value = ""
    
    byggratt.Value = ""
    
    markyta.Value = ""
    
    planstatus.Value = ""
    
    bestallare.Value = ""
    
    saljare.Value = ""
    
    kontaktperson.Value = ""
    
    kopeskilling.Value = ""
    
    projektomkostnad.Value = ""
    
    byggstart.Value = ""
    
    ansvarig.Value = ""
    
    anm.Value = ""
    
    End Sub
    


    Monday, October 19, 2015 8:17 AM

All replies

  • Your friend's Excel 2008 for Mac was not shipped with VBA, it was re-introduced in Excel 2011
    Monday, October 19, 2015 10:07 AM
  • Hi. Thanks.

    Does this mean updating to the most recent (or Office 2011) will
    make it work? No rewriting of the code necessary? 

    Monday, October 19, 2015 10:55 AM
  • If it works in your 2011 it should work in your friend's if he upgrades to 2011 or 2016. He will probably need to configure his security to allow macros after installing. 
    Monday, October 19, 2015 1:34 PM
  • There may be other reasons your userform will not work as expected on the Mac.

    Userforms which are properly sized in Windows will appear very small on the Mac. There are ways to get around this, once the userform successfully appears on the Mac in any size.

    If you use any images in the picture property of any of the controls on the userform, they must be 24-bit bitmaps. No PNGs, GIFs, JPGs, or WMFs. The wrong image type may simply not appear on the userform, but it may also make other controls not appear, and it may in fact cause the entire form to be blank or not to appear at all.


    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services, Inc.
    Peltier Tech Blog

    Monday, October 26, 2015 11:01 PM