Is there a userform deactivate command?

[Botanybob]

I would like the userform to be at the center of screen when activated (as normal) then move to the very bottom of screen when the worksheet behind it is clicked.

I made my worksheet modeless and made a command button to do what I want, but I want the "deselect userform" command.

This is what I have:

 Private Sub CommandButton1_Click()
   With Me
        .StartUpPosition = 0
        .Top = 630
        .Left = 0
    End With
End Sub

Thanks a bunch!

 

[My Aswer Is This]

Learned something new here today. Never learned before of "UserForm1.Show vbModeless"

 

[Norie]

BotanyBob

It's really just JLGWhiz's code with a minor adjustment.

 

[Botanybob]

This new code has presented a little bit of a problem. When users press the ok button on the userform it updates the worksheet with information from the userform (as normal), but now hides the userform. If you want the userform to appear to remain open after ok is clicked a "show userform" command must be written into userform code. This fix works, but there is a brief lag in screen updating even if "screen updating" is false.

 

[Norie]

How are you writing the data to the worksheet?

Does it involve any selecting?

 

[Botanybob]

Yes, I am selecting cells in worksheet. I will have to find another way.

Sheets("Data").Activate


Range("A4").Select
    ActiveCell.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Range("A4:T4").Borders.LineStyle = xlContinuous


Cells(4, 2) = tbTime.Text

 

[Norie]

Well you don't actually need to Select/Activate so altering your code to get rid of that would help.

Not 100% sure how to do that right now - getting a bit late here.

In the meantime you could add this to your code to stop the SelectionChange event being triggered.

Application.EnableEvents = False

If you do add that make sure you turn events back on with this, after you've finished transferring data to the worksheet.

Application.EnableEvents = True

 

[Botanybob]

I tried to insert your code before and after what I posted above and it didn't work.
I'm guessing the hide userform code in worksheet is overriding it.
I will try and find another way to insert a row than the cell selection method.
Thanks for trying it means a lot.

 

[Norie]

The code I posted should stop the SelectionChange event being triggered, in fact it should stop all events being triggered.

Where exactly did you put the code I suggested?

 

[Botanybob]

Well, actually I took another look at it and there was another select event (data sort) in the userform that was selecting the worksheet. I moved your enable events code right after the command click and before the end sub and it totally worked.
I'm going to list this thread as closed.
Thanks a ton everyone!!!