Forms are multiplying all over the place

[mrdod]

I'm calling a macro in another workbook (x) from my personal macro workbook. I'm opening a form from workbook x and when it opens and I drag the form I get multiples of the form appearing. It seems like it only happens when I use application.screenupdating = False. Is there a way around this so the user doesn't have to see the other workbook opening?

Here is the code:
Application.ScreenUpdating = False
Workbooks.Open "C:\Documents and Settings\xxxxx\Desktop\orderboard.xls"
Application.Run ("orderboard.xls!Open_Form")
Workbooks("orderboard.xls").Close

Thank you for your time!!

 

[PHV]

You may try this:
Application.ScreenUpdating = False
Workbooks.Open "C:\Documents and Settings\xxxxx\Desktop\orderboard.xls"
[!]Windows("orderboard.xls").Visible = False
Application.ScreenUpdating = True[/!]
Application.Run ("orderboard.xls!Open_Form")
Workbooks("orderboard.xls").Close

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mrdod]

PH,

Thanks for the help but when I run the code the sheet I have open isn't visible. I am just opening a new workbook and using a hot key to run the program from my personal macro workbook.

Application.ScreenUpdating = False
Workbooks.Open "C:\Documents and Settings\mtaylor\Desktop\orderboard.xls"
Windows("orderboard.xls").Visible = False
Application.ScreenUpdating = True
Application.Run ("orderboard.xls!Open_Form")
Workbooks("orderboad.xls").Close

Is it because I'm running it from my personal macro workbook?

 

[TonyJollans]

There is no obvious reason for this in what you've posted. What is the code in orderboard.xls!Open_Form? And, if the Form has any Initialize or Activate Event Code, what is that?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[mrdod]

Sorry Tony.

This is the code for the form (Initialize).

Private Sub UserForm_Initialize()

Dim dtmdate As Date
Dim n As Integer

dtmdate = #1/1/2007#
For n = 1 To 52
Me.cbstartdate.AddItem dtmdate
dtmdate = dtmdate + 7
Next n

With Me.cbshifts
.AddItem "1"
.AddItem "2"
.AddItem "3"
End With
End Sub

I've had this problem with Excel forms before when I use ScreenUpdating = False and was hoping there was a fix. Sorry again and thanks for trying to help.

 

[TonyJollans]

Does Repainting the UserForm help at all? Or DoEvents?

I'm never entirely sure of the scope of ScreenUpdating but in my innocence I kind of expect the screen to remain unchanged while it is in effect - which would stop a UserForm displaying properly especially, perhaps, when you are making runtime changes to combos on it.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[mrdod]

Hmmmm I'm not entirely sure what happened but the form stopped doing it's funky multiplying thing without me changing any of the original code. This obviously worries me because it may come back some other time. Sorry if I wasted your time with this issue but your theory is an interesting one about why the form may not display properly. Thanks and SORRY once again.

 

[TonyJollans]

No need to apologise!

I didn't register originally but the second time you said it, it stirred a dim memory about form display and screenupdating to which I think Repaint may have been the solution.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association