suznal


I have a userform that is called from a keyboard shortcut. It works perfectly fine when I run it, but other users cannot run it on their machines. We all are using Excel 2003 SP3, (only one user has SP2) and all users have changed their security to allow macros. But when they try to load the form they get a compile error.

The code I have for the userform is...

Code Block

Private Sub UserForm_Activate()
TextBox7.Value = Sheet24.Range("A10")
TextBox17.Value = Sheet24.Range("B10")
TextBox16.Value = Sheet24.Range("A11")
TextBox26.Value = Sheet24.Range("B11")
TextBox15.Value = Sheet24.Range("A12")
TextBox25.Value = Sheet24.Range("B12")
TextBox14.Value = Sheet24.Range("A13")
TextBox24.Value = Sheet24.Range("B13")
TextBox13.Value = Sheet24.Range("A14")
TextBox23.Value = Sheet24.Range("B14")
TextBox12.Value = Sheet24.Range("A15")
TextBox22.Value = Sheet24.Range("B15")
TextBox11.Value = Sheet24.Range("A16")
TextBox18.Value = Sheet24.Range("B16")
TextBox10.Value = Sheet24.Range("A17")
TextBox19.Value = Sheet24.Range("B17")
TextBox9.Value = Sheet24.Range("A18")
TextBox20.Value = Sheet24.Range("B18")
TextBox8.Value = Sheet24.Range("A19")
TextBox21.Value = Sheet24.Range("B19")
uSheets = Array(Sheet10, Sheet11, Sheet13, Sheet12, Sheet14, Sheet29)
a = 1
For Each sSheet In uSheets
On Error Resume Next
With sSheet
If .CheckBox1.Value = True Then
Me.Controls("textbox" & a).Text = "1"
ElseIf .CheckBox2.Value = True Then
Me.Controls("textbox" & a).Text = "2"
ElseIf .CheckBox3.Value = True Then
Me.Controls("textbox" & a).Text = "3"
End If
End With
a = a + 1
On Error GoTo 0
Next
Me.Repaint

End Sub

(special thanks to Cringing Dragon)

And it is called from a keyboard shortcut...

Code Block

Sub UserForm1Open()
'
' Keyboard Shortcut: Ctrl+q
'
UserForm1.Show
End Sub

Which is saved in a module on the workbook.

When users try to run it they get a compile error on...

Code Block
uSheets = Array(Sheet10, Sheet11, Sheet13, Sheet12, Sheet14, Sheet29)

The debugger says the "array is hidden".

I have no problem running it so I'm not quite sure what to make of it, any ideas





Re: Excel Userform - Runs fine but other users get a compile error

bi-lya


Hi

Try to change the following line like here:

Code Block

uSheets = Array("Sheet10", "Sheet11", "Sheet13", "Sheet12", "Sheet14", "Sheet29")
.....
.....
With Worksheets(sSheet)






Re: Excel Userform - Runs fine but other users get a compile error

suznal

bi-lya, when I apply your changes I'm back to the problem I had before; all of the textboxes show a value of one (1) regardless of what checkboxes are checked.







Re: Excel Userform - Runs fine but other users get a compile error

bi-lya

Would you take out all your code from module, module of form and sheets here IMHO, you have mistake somewhere...

Did you try to use the code, which I'd given for you last time Make it in new book and - maybe - you will find some cause





Re: Excel Userform - Runs fine but other users get a compile error

suznal

bi-lya, Ok, I created a NEW workbook with a userform. I made the changes to the code as you suggested and distributed to several users for testing, it worked.

Then, I added code that is on the worklsheet for the checkboxes, (only one checkbocx per group can be true, remember, see below)...and once again sent it out to users for testing.

Code Block

Public Sub SingleCheckbox(OnSheet As Worksheet, ThisControl As Object)
'
' Allow only one CheckBox per group a value of True
'

Dim oleTemp As OLEObject

If ThisControl.Object.Value Then
For Each oleTemp In OnSheet.OLEObjects
If TypeName(oleTemp.Object) = "CheckBox" Then
If oleTemp.Object.GroupName = ThisControl.Object.GroupName Then
If oleTemp.Name <> ThisControl.Name Then
oleTemp.Object.Value = False
End If
End If
End If
Next
End If

End Sub


Private Sub CheckBox1_Click()
SingleCheckbox ActiveSheet, CheckBox1
End Sub

Private Sub CheckBox2_Click()
SingleCheckbox ActiveSheet, CheckBox2
End Sub

Private Sub CheckBox3_Click()
SingleCheckbox ActiveSheet, CheckBox3
End Sub

Some people could load the userform, some people couldn't. Everyone was sent the same workbook, so I don't think it is an issue of errors in the macros.

I also re-sent the workbook with my code but removed the code above (only one checkbox per group), and all could open it, but with that code running only some people, (same ones).

Maybe just individual users settings






Re: Excel Userform - Runs fine but other users get a compile error

bi-lya

You need:
1. Declare variables in the module of the form

Code Block
Dim uSheets As Variant
Dim sSheet as Variant
Dim a as Integer


2. Use names of sheets

Code Block
uSheets = Array("020-100-F22", "020-100-F23", "020-100-F24", "020-100-F25", "020-100-F26", "020-100-F27")
...
With Worksheets(sSheet)

...

You may remove On Error... and Me.Repaint


3. Write ONLY ONE Sub SingleCheckbox in Module1 and remove (delete) all in modules of sheets


4. You'd batter make ONLY ONE Public Sub in Module1 to load pictures and remove all in modules of sheets

For example:
write on the sheet "020-100-F22" :

Code Block

Private Sub CommandButton1_Click()
LoadPik ("A31")
End Sub

Private Sub CommandButton2_Click()
LoadPik ("F31")
End Sub

Write in Module1:


Code Block
Public Sub LoadPik(picRange As String)
Dim PicLocation As String
Dim MyRange As String

ActiveSheet.Unprotect
Range(picRange).Select
MyRange = Selection.Address
PicLocation =...
...
End With
Range(picRange).Offset(-1).Select
ActiveSheet.Protect
End Sub





Re: Excel Userform - Runs fine but other users get a compile error

suznal

Thanks bi-lya!

All seems to work fiine on my machine. The code is a lot neater and the workbook loads a lot faster now. I will send it out for users to test and let you know how it went.






Re: Excel Userform - Runs fine but other users get a compile error

suznal

bi-lya, all works well.

I did find what I was doing wrong though.

I was entering...

Code Block
For Each sSheets In uSheets

instead of...

Code Block

For Each sSheet In uSheets