userform not working on another machine

troyaaron

New Member
Joined
May 20, 2013
Messages
33
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:

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:

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

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

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,524
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
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
Code:
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
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

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