Is there a userform deactivate command?

JLGWhiz

Well-known Member

Joined

Feb 7, 2012

Messages

12,979

Office Version

1. 2013

Platform

1. Windows

• Aug 22, 2014

• #2

Botanybob said:

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:

Code:

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

Thanks a bunch!

Click to expand...

To deactivate a userform, you use the Unload or Hide method. In your case, you could try the hide method since it is assumed you meant the userform is modeless.

 

Last edited: Aug 22, 2014

Upvote 0

B

Botanybob

New Member

Joined

Jul 26, 2014

Messages

18

• Aug 22, 2014

• #3

I would like for the data that has been entered into the userform to remain intact so I can't use the unload command, and if I hide it how do I bring it back?
Basically the move command I have above would "minimize" the userform (move it out of the way). I guess I would also need the command to "activate" the userform as well so I can bring it back to original position.

 

Upvote 0

JLGWhiz

Well-known Member

Joined

Feb 7, 2012

Messages

12,979

Office Version

1. 2013

Platform

1. Windows

• Aug 22, 2014

• #4

Code:

UserForm1.Show

This is essentially the activate command. There is also a Load method, but the Show method both loads and makes the form visible. This command works to initially show the form or to bring it back after it is hidden.

Maybe this link would be helpful.

Excel VBA Userform - Easy Excel Macros

 

Upvote 0

B

Botanybob

New Member

Joined

Jul 26, 2014

Messages

18

• Aug 22, 2014

• #5

Ok, I am still missing something here.

Here are the steps:
1. Push a command button on worksheet and activate userform1 with

Code:

UserForm1.Show vbModeless

2. Now userform is on screen
3. I want to select a cell in the worksheet and have that action trigger a hide userform event
4. I guess I can click the same command button on worksheet that I did before to show the userform again.

Please tell me how to do number 3

 

Upvote 0

JLGWhiz

Well-known Member

Joined

Feb 7, 2012

Messages

12,979

Office Version

1. 2013

Platform

1. Windows

• Aug 22, 2014

• #6

I think this would work.
The code should be copied to the Worksheet code module for the sheet where you want to click. To install the code, right click the sheet name tab, then click 'View Code' in the pop up menu. When the code window opens, copye the code below into it, close the window and save the file. It should now hide the visible userform when you select any new cell on the worksheet.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 If UserForm1.Visible = False Then
  UserForm1.Hide
End Sub

 

Last edited: Aug 22, 2014

Upvote 0

B

Botanybob

New Member

Joined

Jul 26, 2014

Messages

18

• Aug 23, 2014

• #7

Sorry JLG that did not work.
I don't follow that logic either.
The userform is still open and visible when I click on a cell in the workbook, so it doesn't hide the userform.
Do I just need to make a "hide userform" button on my userform, or is there a way to hide the userform when a cell on the worksheet behind it is activated with mouse click?

 

Upvote 0

My Aswer Is This

Well-known Member

Joined

Jul 5, 2014

Messages

19,511

Office Version

1. 2021

Platform

1. Windows

• Aug 23, 2014

• #8

You can double click your UserForm in the tool box when creating your UserForm. Put in the text "Me.Hide". Then any time you click the UserForm window it will hide the UserForm. Avoiding making another button.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Aug 23, 2014

• #9

I think it should be this in the SelectionChange event.

Code:

    If UserForm1.Visible = True Then
        UserForm1.Hide
    End If

This will hide the userform when you make a selection on the worksheet, you can display it again by pressing the button that initilally shows it.

 

Upvote 0

B

Botanybob

New Member

Joined

Jul 26, 2014

Messages

18

• Aug 23, 2014

• #10

Norie, that did it. Thank you so much

The following code placed in the worksheet hides the userform when a cell in the worksheet is activated with a mouse click.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If UserForm1.Visible = True Then
        UserForm1.Hide
    End If
End Sub

 

Last edited: Aug 23, 2014

Upvote 0