?
Solved

Seeing if a form has loaded

Posted on 2003-03-06
6
Medium Priority
?
158 Views
Last Modified: 2010-05-01
Hey,
I'm using VBA in excel and i've got a little problem.  I have an if statement, that is lengthy unfortunately.  I only want something to happen if 4 different things come out true.  so this is the way i have it right now:

If ActiveCell.Column > 9 And ActiveCell.Column < 14 And ActiveCell.Row <> 1 And pinorspec.Visible = False and splineinput.visible = false Then
     ......
     end if

the thing i don't like is that checking the visible property of those forms is taking a little time.  it's actually not that long, but the hourglass appears for just a split second and dissappears.  it basically pauses for just a second.  i know it's the form stuff giving me problems b/c checking those cell properties takes no time from the testing i've done.  so, here's the question.  is there a way to check whether or not a form is loaded or not?  
also, if you can find a way to trim down that if statement it would be appreciated.  this all happens in the sub Worksheet_SelectionChange area.  i'm basically just waiting for a selection to be made to a certain area of the worksheet and when it happens, pop up the forms, unless they are already loaded.  
Also, is there a way to pass values between those forms in excel VBA?  i've tried the whole "Public Dim xyz as Integer" etc, and they don't get passed between the forms for some reason.  sorry for the lengthy question, jsut trying to cover all my bases, thx guys.
0
Comment
Question by:kidgenius2002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 10

Expert Comment

by:aeklund
ID: 8082552
Breaking it down into multiple if statements will speed up your overall code..

If ActiveCell.Column > 9 And ActiveCell.Column < 14 Then
  If ActiveCell.Row <> 1 Then
    If pinorspec.Visible = False and splineinput.visible = false Then
      .....
    End if

  End If

End If

I will check on the detection of the form being loaded and get back to you...
0
 
LVL 10

Accepted Solution

by:
aeklund earned 300 total points
ID: 8082591
Here is how I would do it...

On each form add code to these sections:
Private Sub UserForm_Initialize()
 myform1loaded = true
End Sub

Private Sub UserForm_Terminate()
 myform1loaded = false
End Sub


where myform1loaded is a public variable in a module...  then you don't need to check the forms visible property, you could just reference the variables...

If myform1loaded = False and myform2loaded = false Then
     .....
End if
0
 

Author Comment

by:kidgenius2002
ID: 8082627
how do i define that variable in the module though? i don't have nay modules running, just the forms.  it seems like i would have to have something define the variables in the module.  would it be something like
sub worksheets.open
     call loadvars
     end sub

where loadvars is a public sub in a module

would that be what u r talking about?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:kidgenius2002
ID: 8082715
how do i define that variable in the module though? i don't have nay modules running, just the forms.  it seems like i would have to have something define the variables in the module.  would it be something like
sub worksheets.open
     call loadvars
     end sub

where loadvars is a public sub in a module

would that be what u r talking about?
0
 

Author Comment

by:kidgenius2002
ID: 8082846
i was able to get that to work. i just create a module that gets run when the spreadsheet opens that will declare those variables.  thx
0
 
LVL 10

Expert Comment

by:aeklund
ID: 8083045
correct...

your module should only have something like this:


public myform1loaded as boolean
public myform2loaded as boolean


you don't need to load the variables, you only set them on the initialize and terminate events of your form.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month14 days, 3 hours left to enroll

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question