userform not working on another machine

[troyaaron]

Hi,

Rick Rothstein helped me create a nifty userform to find commented cells. Works great!

However I need to be able to send this to other users and currently it only works on my machine!

Please help me.

The Userform code:

Dim CurrentComment As Long, PreviousCell As Range




Private Sub CmdcNext_Click()
  PreviousCell.Interior.ColorIndex = xlColorIndexNone
  CurrentComment = CurrentComment - (CurrentComment < ActiveSheet.comments.Count)
  Set PreviousCell = Range(ActiveSheet.comments(CurrentComment).Parent.Address)
  Cellcommentsa = ActiveSheet.comments(CurrentComment).Text
  Cellcontentsa = ActiveSheet.comments(CurrentComment).Parent.Value
  Cellrefa = Chr(ActiveSheet.comments(CurrentComment).Parent.Column + 64) & ActiveSheet.comments(CurrentComment).Parent.Row
  PreviousCell.Select
  PreviousCell.Interior.ColorIndex = 46
End Sub




Private Sub CmdcPrev_Click()
  PreviousCell.Interior.ColorIndex = xlColorIndexNone
  CurrentComment = CurrentComment + (CurrentComment > 1)
  Set PreviousCell = Range(ActiveSheet.comments(CurrentComment).Parent.Address)
  Cellcommentsa = ActiveSheet.comments(CurrentComment).Text
  Cellcontentsa = ActiveSheet.comments(CurrentComment).Parent.Value
  Cellrefa = Chr(ActiveSheet.comments(CurrentComment).Parent.Column + 64) & ActiveSheet.comments(CurrentComment).Parent.Row
  PreviousCell.Select
  PreviousCell.Interior.ColorIndex = 46
End Sub




Private Sub CmdcStop_Click()
  Unload Me
End Sub




Private Sub Label1_Click()


End Sub


Private Sub UserForm_Initialize()
  CurrentComment = 1
  With ActiveSheet.comments(1)
    Cellcommentsa = .Text
    Cellcontentsa = .Parent.Value
    Cellrefa = Chr(.Parent.Column + 64) & .Parent.Row
    Set PreviousCell = Range(.Parent.Address)
    PreviousCell.Select
    PreviousCell.Interior.ColorIndex = 46
  End With
'With Cellcontentsa
'    .AutoSize = False
'    .Height = 30
'    .Width = 198
'    .Caption = ""
'    .AutoSize = True
'    .Top = Label2.Top + ((Label2.Height - .Height) / 2)
'    .Left = Label2.Left + ((Label2.Width - .Width) / 2)
'End With
End Sub




Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  PreviousCell.Interior.ColorIndex = xlColorIndexNone
End Sub

I activate this from this code in workbook:

Private Sub Workbook_Open()
With UserForm2
    '.Top = Application.Height / 2 - (UserForm1.Height / 2)
    '.Left = Application.Width / 2 - (UserForm1.Width / 2)
    .Top = Application.Top '+ 25
    .Left = Application.Left '+ Application.Width - UserForm1.Width - 25
    .Show vbModeless
End With
End Sub

As soon as i open the file on another machine i get a compile error - "Wrong number of arguments or invalid property assignment"

It doesn't like the .Show

If I try to run the form from the Userform_Initialize Sub I get a runtime error 32809 "Application-defined or object-defined error"
It doesn't like the

With Activesheet.comments(1)

I can get the form to open if I change the sub to: Userform2_Initialize() (userform2 being the name of the form)

Now the form is open, if i click on a button i get an error. For example:

If i click the Next button (Cmdcnext), i get a runtime error 91 - "Object variable or with block variable not set"
and it points to

PreviousCell.Interior.ColorIndex = xlColorIndexNone

I just want it to work as it does on my machine.....

the destination machine is excel 97 with object 8.0 library

mine is excel 2013 with object 14.0 library

is this why? is there any way around this?

 

[mole999]

I tend to use
Option explicit 'at the top of each module

I've had .show issues when a calender form was called in 2010 which isn't available

this will help you identify the GUIDs

Private Sub GetGUID()
    Dim ref
    For Each ref In ThisWorkbook.VBProject.References
Debug.Print ref.Name, ref.GUID, ref.Major, ref.Minor
    Next
End Sub

I then use this to add missing references when loading onto a foreign machine

Private Sub Workbook_Open()

    Dim ID
    'Exit Sub
    On Error Resume Next
    'Reference ADO Object Library using Major / Minor GUID
    Set ID = ThisWorkbook.VBProject.References
    'ID.AddFromGuid "{00000205-0000-0010-8000-00AA006D2EA4}", 2, 5 'Microsoft ActiveX Data Objects 2.5
    ID.AddFromGuid "{00000206-0000-0010-8000-00AA006D2EA4}", 2, 5        'Microsoft ActiveX Data Objects 2.6
    ID.AddFromGuid "{00025E01-0000-0000-C000-000000000046}", 2, 5        'Microsoft DAO 3.6
    ID.AddFromGuid "{8E27C92E-1264-101C-8A2F-040224009C02}", 7, 0        'MSCAL.OCX 2007
    ID.AddFromGuid "{3050F1C5-98B5-11CF-BB82-00AA00BDCE0B}", 4, 0        'HTML
    ID.AddFromGuid "{EAB22AC0-30C1-11CF-A7EB-0000C05BAE0B}", 1, 1        'IE

I have a feeling you are stuffed going back to 97 I'm not sure it was capable of that level