Help with userform

[74Pumpkin]

I am trying to get a userform to pop up while a function is done in the background. I have got as far as it poping up, then it stops. I have to close to form for the function to finish. Any ideas?

Sub addnew()
' addnew Macro
UserForm2.Show
Columns("L:L").Select
Range("L2").Activate
Selection.Insert Shift:=xlToRight
Columns("I:L").Select
Range("L2").Activate
Selection.EntireColumn.Hidden = False
Columns("K:K").Select
Range("K2").Activate
Selection.Copy
Columns("L:L").Select
Range("L2").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Columns("K:K").Select
Range("K2").Activate
Selection.EntireColumn.Hidden = True
UserForm2.Hide
End Sub

[johnske]

Hi 74pumpkin,

Welcome to VBAX.

One thing that springs to mind is to have UserForm2.Show as the only line in your Sub and put the rest of your code on the userform under Private Sub UserForm_Activate().

Hope this helps,
John

[Jacob Hilderbrand]

When the User Form is shown all other code will stop and will not continue until the User Form is hidden or unloaded. You can put the code in the Activate sub like John suggests and then the code can continue after the User Form is shown.

[Jacob Hilderbrand]

Also your code can be cleaned up a bit.

Columns("L:L").Insert
    Columns("I:L").EntireColumn.Hidden = False
    Columns("K:K").Copy Destination:=Columns("L:L")
    Columns("K:K").EntireColumn.Hidden = True

[johnske]

Yes - also - if the idea of showing the form is to give a message such as "Processing, please wait", have a look at the "Application.ScreenUpdating = False" function.

It usually speeds things up enormously (you may not even need the userform)

[zilpher]

You can have a userform show whilst code continues in the background (although like the others I don't think it's necessary here if you use John and Jacobs suggestions).

To achieve this, check out the show method of the userform object; it has one optional argument called Modal, of the type Boolean, that controls the forms modality. If you set it to false, the userform is shown non-modal and you can continue to run other code, use excel etc. whilst the userform is being displayed.

Sub foo()
     UserForm2.Show False
     Columns("L:L").Insert
     Columns("I:L").EntireColumn.Hidden = False
     Columns("K:K").Copy Destination:=Columns("L:L")
     Columns("K:K").EntireColumn.Hidden = True
     Unload UserForm2
 End Sub

I changed your userform2.hide to an unload, it's cleaner to unload the form rather than hide it.

You can also change the modal property of the form in it's properties window, the property is called ShowModal and defaults to True. If you change that to false, you don't have to pass False to the Show method.

HTH

[74Pumpkin]

Thanks guys this works just as I was hoping. Only thing left is the userform comes up blank. Just plain white, no lables ect. I did as was suggested and called the userform up and put the rest of the code on the userform with the on screen update off.
( It will still run slow due to the really slow pentium 1 lap top being used. and the users are quick to say its locked up, hence the need.)
So what am I doing wrong here?

Private Sub UserForm_Activate()
    Application.ScreenUpdating = False
    Columns("L:L").Insert
    Columns("I:L").EntireColumn.Hidden = False
    Columns("K:K").Copy Destination:=Columns("L:L")
    Columns("K:K").EntireColumn.Hidden = True
    Application.ScreenUpdating = True
    Unload UserForm2
End Sub

I know this is likely a real newbie problem, ( Which I am) I'm just getting into VBA and think I need to take a class or get a book or something to help me along.

Thanks for the help!

Jeff

[johnske]

Sorry Jeff, I really should have been more explicit. I meant for you to try putting ScreenUpdating false to speed things up INSTEAD of using the userform.

I thort my computer (running at 233MHz) was slow, but your macro only took the blink of an eye to complete on my machine. If you're running something slower I see the point of the userform.

Remove the reference to ScreenUdating from the userform as it's preventing information about the userform being displayed (updated), your label should then be shown properly
I.E.Try this

Private Sub UserForm_Activate() 
    Columns("L:L").Insert 
    Columns("I:L").EntireColumn.Hidden = False 
    Columns("K:K").Copy Destination:=Columns("L:L") 
    Columns("K:K").EntireColumn.Hidden = True 
    Unload Me 
End Sub

[74Pumpkin]

Thanks for all the help Johnske. The form is still coming up blank ( White ) I do have things on the form. See if I'm, doing this correct. I have a command button calling up a marco.

Sub addnew()
UserForm2.Show
End Sub

This calls up userform 2

Private Sub Label1_Click()
End Sub
Private Sub UserForm_Activate()
Columns("L:L").Insert
Columns("I:L").EntireColumn.Hidden = False
Columns("K:K").Copy Destination:=Columns("L:L")
Columns("K:K").EntireColumn.Hidden = True
Unload UserForm2
End Sub

Every thing works great except I cannot get anything to show up on the form. When i take out the command to unload the form it shows fine after the column has been added and the form is no longer needed. I'm sure it something simple I'm missing.

Thanks again for the help.

[johnske]

Hi Jeff,

Try this as an alternative to a userform - it's probably quicker...(This goes in an ordinary module and you can change A1 to any place where you want the message to show)

Sub LetsTryIt()
      '//add a comment
      With Range("A1")
            .Select
            .AddComment
            .Comment.Visible = True
            .Comment.Text Text:="Processing - please wait"
            .Comment.Shape.Select True
            Selection.Font.Size = 16
      End With
'//now run your macro
      Application.ScreenUpdating = False
      Columns("L:L").Insert
      Columns("I:L").EntireColumn.Hidden = False
      Columns("K:K").Copy Destination:=Columns("L:L")
      Columns("K:K").EntireColumn.Hidden = True
'//now remove the comment
      Range("A1").Select
      Selection.ClearComments
      Application.ScreenUpdating = True
End Sub

[johnske]

Another option (if you have the office assistant installed but don't normally use it) is to amuse yourself with the assistants antics while you're waiting... E.G. [vba]Option Explicit

Sub LetsTryThis()
'//show the assistant
      With Assistant
            .Visible = True
            .Sounds = True
            .Animation = msoAnimationGetTechy
      End With
'//now run your macro
      Application.ScreenUpdating = False
      Columns("L:L").Insert
      Columns("I:L").EntireColumn.Hidden = False
      Columns("K:K").Copy Destination:=Columns("L:L")
      Columns("K:K").EntireColumn.Hidden = True
'//hide the assistant
      Assistant.Visible = False
End Sub

[74Pumpkin]

I really like that add comment. I think I willl find a use for that. I also found out what I was doing wrong. I added a DoEvent and it works great!

thanks for all the help on this one.
I have another thing I'm trying to fix but I'll start another thread for that.

Thanks Johnske, DRJ and Zilpher! this helped out alot!

[johnske]

Hi Jeff,

Sorry, I only just realized I didn't answer your previous question fully...

The white userform is caused by there simply not being enough TIME for the form - and all controls, labels etc on it - to be fully activated before processing passes to the main body of code (inserting columns etc).

The solution to this is to put in some sort of deliberate pause or delay to allow more time for the form to be activated before running the rest of the code. This pause actually slows down the entire processing, but it may not be enough to be noticeable.

Try the code below (in the userform module). On my computer (233MHz), one hundredth of a seconds delay is sufficient time to activate the form and show the labels text, if the following code doesn't work on yours, try extending it to "Pause 0.1" or "Pause 1" etc until it works...(the "Pause" time is in seconds)

Regards,
John

Option Explicit
Public StartTime
Private Sub UserForm_Activate()
Pause 0.01
Application.ScreenUpdating = False
Columns("L:L").Insert
Columns("I:L").EntireColumn.Hidden = False
Columns("K:K").Copy Destination:=Columns("L:L")
Columns("K:K").EntireColumn.Hidden = True
Application.ScreenUpdating = True
Unload Me
End Sub
 
Function Pause(DelayInSeconds)
Dim StartTime
StartTime = Timer
Do While Timer < StartTime + DelayInSeconds
DoEvents
Loop
End Function