PDA

View Full Version : Hide userform by clicking outside form



rabarber
12-19-2008, 01:25 AM
Hello

I want to hide my user form not when i click on some button, but when i just click outside the form.

Is there a way to insert column, without changing columns alphabetic letters ? For example, can i between column A an column B insert column with letter AX ?

I am not using list on my table, so when i click on column letter it selects whole column including first row, but i need to select whole column except first row, so when i want to clear column contents first row (column title) stay.

Thanks for helping :)

Jan Karel Pieterse
12-19-2008, 02:49 AM
Quite different questions you have here.

1: can be done.

Put this code in the module behind your form:


Option Explicit
Private WithEvents oApp As Application
Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Me.Hide
End Sub
Private Sub UserForm_Initialize()
Set oApp = Application
End Sub


2. Impossible, all you can do is create your own headings in the first row of the table

3. All you can do is select the cells you need.

Kenneth Hobs
12-19-2008, 07:14 AM
For (1), be sure to set ShowModal=False or show the userform as vbmodeless.

For (3), select the whole column by clicking the column letter as you did. To toggle one of the selections, hold the Ctrl key down and click the cell to toggle selection but keep the other cells selected.

Jan Karel Pieterse
12-19-2008, 07:28 AM
Duh, forgot to add my module code with...
Userform1.Show vbModeless

rabarber
12-20-2008, 12:23 AM
Thanks for your help alot.

vbModeless was thing what i really needed.
If Target.Column = 9 Then
UserForm1.Show vbModeless
Else
UserForm1.Hide
End If

I solve my problem with code like this :)

thanks again