• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 160
  • Last Modified:

Seeing if a form has loaded

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
kidgenius2002
Asked:
kidgenius2002
  • 3
  • 3
1 Solution
 
aeklundCommented:
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
 
aeklundCommented:
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
 
kidgenius2002Author Commented:
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
Technology Partners: 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!

 
kidgenius2002Author Commented:
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
 
kidgenius2002Author Commented:
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
 
aeklundCommented:
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now