Worksheet appears briefly before UserForm opens

[Rob Flott]

I am in Excel 2007 and have a workbook with a UserForm and several
worksheets. I set it so when the workbook is opened it will open the
UserForm. Furthermore when the UserForm is activated it launches a
macro that loads a list of names into the first ListBox in the
UserForm.

Anyway, the problem I have is when I open the workbook a portion of
one worksheet appears for a few seconds and then the UserForm opens
and everything is fine from there on. I've looked all over and cannot
figure why this one worksheet always appears first before the UserForm
opens.

Does anyone have any ideas?

TIA,

Rob

 

[GS]

Rob Flott formulated on Wednesday :

I am in Excel 2007 and have a workbook with a UserForm and several
worksheets. I set it so when the workbook is opened it will open the
UserForm. Furthermore when the UserForm is activated it launches a
macro that loads a list of names into the first ListBox in the
UserForm.

Anyway, the problem I have is when I open the workbook a portion of
one worksheet appears for a few seconds and then the UserForm opens
and everything is fine from there on. I've looked all over and cannot
figure why this one worksheet always appears first before the UserForm
opens.

Does anyone have any ideas?

TIA,

Rob

Can you post the code?

 

[GS]

I am in Excel 2007 and have a workbook with a UserForm and several
worksheets. I set it so when the workbook is opened it will open the
UserForm. Furthermore when the UserForm is activated it launches a
macro that loads a list of names into the first ListBox in the
UserForm.

Anyway, the problem I have is when I open the workbook a portion of
one worksheet appears for a few seconds and then the UserForm opens
and everything is fine from there on. I've looked all over and cannot
figure why this one worksheet always appears first before the UserForm
opens.

Does anyone have any ideas?

TIA,

Rob

I tried this using Workbook_Open event and the worksheet fully displays
as usual, and the userform displays as expected. Not sure why you only
get partial worksheet. (The userform is blank, FYI)

 

[Rob]

Rob Flott formulated on Wednesday :








Can you post the code?

Garry,

Here is my code beginning with the workbook;

Private Sub Workbook_Open()
UF1.Show
End Sub
*************************************
'UF1:
Private Sub UserForm_Activate()
With UF1
.OB1.Value = False
.OB2.Value = False
.OB30.Value = False
.OB31.Value = False
.OB32.Value = False
.OB33.Value = False
.OB34.Value = False
.OB35.Value = False
.OB36.Value = False
.OB37.Value = False
End With

With UF1.Label3
.Visible = False
End With

Call LoadProdList2

End Sub
****************************************
Sub LoadProdList2()
Sheets("list").Select
Range("a1").Select

Dim AllCells As Range, cell As Range
Dim NoDupes As New Collection
Set AllCells = Sheets("list").Range("A2:A78")
On Error Resume Next
For Each cell In AllCells
NoDupes.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0
For Each Item In NoDupes
UF1.LB1.AddItem Item
Next Item

With UF1
.Label3.Visible = False
.LB2.SetFocus
End With

End Sub
******************

Garry, thank you for taking time on this...

Rob

 

[GS]

Rob wrote :

Garry,

Here is my code beginning with the workbook;

Private Sub Workbook_Open()
UF1.Show
End Sub
*************************************
'UF1:
Private Sub UserForm_Activate()
With UF1
.OB1.Value = False
.OB2.Value = False
.OB30.Value = False
.OB31.Value = False
.OB32.Value = False
.OB33.Value = False
.OB34.Value = False
.OB35.Value = False
.OB36.Value = False
.OB37.Value = False
End With

With UF1.Label3
.Visible = False
End With

Call LoadProdList2

End Sub
****************************************
Sub LoadProdList2()
Sheets("list").Select
Range("a1").Select

Dim AllCells As Range, cell As Range
Dim NoDupes As New Collection
Set AllCells = Sheets("list").Range("A2:A78")
On Error Resume Next
For Each cell In AllCells
NoDupes.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0
For Each Item In NoDupes
UF1.LB1.AddItem Item
Next Item

With UF1
.Label3.Visible = False
.LB2.SetFocus
End With

End Sub
******************

Garry, thank you for taking time on this...

Rob

Hi Rob,
You might get better results if you 'Load' UF1 and put your code in the
'Initialize' event rather than the 'Activate' event. Then 'Show' it in
its 'Initialize' event.

Example:
Private Sub Workbook_Open()
Load UF1
End Sub

Private Sub UserForm_Initialize()
With Me
.OB1.Value = False: .OB2.Value = False
.OB30.Value = False: .OB31.Value = False
.OB32.Value = False: .OB33.Value = False
.OB34.Value = False: .OB35.Value = False
.OB36.Value = False: .OB37.Value = False
.Label3.Visible = False
End With
Call LoadProdList2
Me.Show
End Sub