UserForm1.Hide is not just to hide UserForm1 ????

Andrew XJ · Mar 18th, 2002, 07:48 AM

Please help me out in this frustrating problem. All this in one workbook with a Auto_Run(). In Auto_Run() i use UserForm1.Show to load and show UserForm1. In UserForm1_initialize(), UserForm1.Show is one of the statements.
In the UserForm1, there is a command button associated with a statement UserForm1.Hide.

The problem is that: When i click the HideUserForm button, an error message pops out "Subscript out of Range". And after i click "debug", the line "UserForm1.Show" in Auto_Run() is highlighted!!!! How come!!!! The click of HideUserForm button just activate one statement UserForm1.Hide......What happens here? UserForm1.Hide is not just to set Visible to False as stated in VBA help, is it???
Could you enlight what happens after UserForm1.Hide on earth? And how about UserForm.Show ?

lenze · Mar 18th, 2002, 08:14 AM

I'm not sure if this is the problem, but you might try using Load Userform1 and Unload Userform1 instead of Show and Hide which are really only toggles.

Andrew XJ · Mar 18th, 2002, 08:40 AM

But Can't use Load method.
Sorry, i haven't described the problem exactly. I tested that again. and the error message is "Run time error 5, Invalid procedure call or property".
The highlighted line is "UserForm1.Show" in Auto_Run(). But if i click "continue" button or press F5, the UserForm appears again, while the error message pops up again when i click the button to hide the UserForm.

When i change UserForm1.Show to Load UserForm1 in Auto_Run, the same error message appears.
In fact i feel that everytime UserForm1.Show runs, the UserForm1_Initialize() will run!!!

best regards
Andrew XJ

Andrew XJ · Mar 18th, 2002, 08:51 AM

Parts of my code get problem: ( in UserForm1_initialization)
UserForm1.Controls("ComboBox1").SetFocus
With UserForm1.Controls("ComboBox1")
.RowSource = "X1:X" & lastRowInSheet1
.Text = Range("X1").Value
sgBox "row source first time set"
Do While (IsParamDirty(.Text))
rowToDelete = Range("X1:X" & _ lastRowInSheet1).Find(what:=.Text, _ LookIn:=xlValues, lookat:=xlWhole, _
SearchOrder:=xlByColumns,MatchCase:=False).Row
Range("X" & rowToDelete & ":Z" & _ rowToDelete).Delete shift:=xlShiftUp

If (Trim(Range("X1").Text) <> vbNullString) Then
lastRowInSheet1 = lastRowInSheet1 - 1
End If
.Text = Range("X" & lastRowInSheet1).Value
Loop
.RowSource = "X1:X" & lastRowInSheet1
End With

This part of code doesn't work.
Is there any difference whether i use .Text or or use .Value? previously i use .Value.

best regards
Andrew XJ

[ This Message was edited by: Andrew XJ on 2002-03-18 07:54 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 18th, 2002, 07:48 AM	#1
Andrew XJ Board Regular Join Date: Feb 2002 Location: Singapore Posts: 77	Please help me out in this frustrating problem. All this in one workbook with a Auto_Run(). In Auto_Run() i use UserForm1.Show to load and show UserForm1. In UserForm1_initialize(), UserForm1.Show is one of the statements. In the UserForm1, there is a command button associated with a statement UserForm1.Hide. The problem is that: When i click the HideUserForm button, an error message pops out "Subscript out of Range". And after i click "debug", the line "UserForm1.Show" in Auto_Run() is highlighted!!!! How come!!!! The click of HideUserForm button just activate one statement UserForm1.Hide......What happens here? UserForm1.Hide is not just to set Visible to False as stated in VBA help, is it??? Could you enlight what happens after UserForm1.Hide on earth? And how about UserForm.Show ?

Mar 18th, 2002, 08:14 AM	#2
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	I'm not sure if this is the problem, but you might try using Load Userform1 and Unload Userform1 instead of Show and Hide which are really only toggles.

Mar 18th, 2002, 08:40 AM	#3
Andrew XJ Board Regular Join Date: Feb 2002 Location: Singapore Posts: 77	But Can't use Load method. Sorry, i haven't described the problem exactly. I tested that again. and the error message is "Run time error 5, Invalid procedure call or property". The highlighted line is "UserForm1.Show" in Auto_Run(). But if i click "continue" button or press F5, the UserForm appears again, while the error message pops up again when i click the button to hide the UserForm. When i change UserForm1.Show to Load UserForm1 in Auto_Run, the same error message appears. In fact i feel that everytime UserForm1.Show runs, the UserForm1_Initialize() will run!!! best regards Andrew XJ

Mar 18th, 2002, 08:51 AM	#4
Andrew XJ Board Regular Join Date: Feb 2002 Location: Singapore Posts: 77	Parts of my code get problem: ( in UserForm1_initialization) UserForm1.Controls("ComboBox1").SetFocus With UserForm1.Controls("ComboBox1") .RowSource = "X1:X" & lastRowInSheet1 .Text = Range("X1").Value sgBox "row source first time set" Do While (IsParamDirty(.Text)) rowToDelete = Range("X1:X" & _ lastRowInSheet1).Find(what:=.Text, _ LookIn:=xlValues, lookat:=xlWhole, _ SearchOrder:=xlByColumns,MatchCase:=False).Row Range("X" & rowToDelete & ":Z" & _ rowToDelete).Delete shift:=xlShiftUp If (Trim(Range("X1").Text) <> vbNullString) Then lastRowInSheet1 = lastRowInSheet1 - 1 End If .Text = Range("X" & lastRowInSheet1).Value Loop .RowSource = "X1:X" & lastRowInSheet1 End With This part of code doesn't work. Is there any difference whether i use .Text or or use .Value? previously i use .Value. best regards Andrew XJ [ This Message was edited by: Andrew XJ on 2002-03-18 07:54 ]