Auto-open a user form

[scottishmovies]

Hi All,

I want to be able to automatically open a user form A when a user click in Col A, and a different user form if they click in Col B. But nowhere else!

Is this possible?

Regards,
Pat

 

[Ruddles]

This in the code module of your worksheet:-

Option Explicit
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
  If Target.Cells.Count = 1 Then
    If Target.Column = 1 Then UserForm1.Show
    If Target.Column = 2 Then UserForm2.Show
  End If
 
End Sub

 

[scottishmovies]

Thanks for such a quick reply Ruddles, excellent.

Just one other thing, how would I make sure that the data entered is then transferred back to that cell?

Or should I have used an input msg box in the first place?

Regards,
Pat

 

[Ruddles]

Just one other thing, how would I make sure that the data entered is then transferred back to that cell?

There are a number of event handlers associated with the form itself and the controls on it - the text boxes, etc. You could:-

• move the data from each control on to the worksheet using its Change or AfterUpdate event (if the fields are largely independent of each other)
• move the data from all of the controls in one go using the userform's Terminate event (if the data is of an all-or-none sort of nature)
• add a command button with an appropriate caption ("Save", "Save and Exit", "Update" or whatever) which moves the data after validating it (if necessary)

The world is, as they say, your pearl-bearing marine bivalve mollusc.

Or should I have used an input msg box in the first place?

I think Inputbox is a bit 'klunky'. "If you can, use a userform; if you can't, use InputBox", as my old gran used to say.

 

[scottishmovies]

I think I'd prefer to use a Save and Exit. But how I do that? I can add the button ok, that's the easy part as far as my limited k nowledge goes but getting it to work now....that's the problem!

For instance if a user clicks in cell A10, the form pops up ok, I now want to get a formatted date and time from them and make sure it goes back into the cell that they clicked on in the first place.

The world may indeed be my oyster - but I'm up the creek without a wooden thing to propel me.

Thanks,
Pat

 

[RoryA]

Make sure your forms can only be hidden and not unloaded. Then, assuming a textbox control on each:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim frm As Object
   If Target.Cells.Count = 1 Then
      If Target.Column = 1 Then
         Set frm = New UserForm1
         frm.Show
         Target.Value = frm.TextBox1.Value
         Set frm = Nothing
      ElseIf Target.Column = 2 Then
         Set frm = New UserForm2
         frm.Show
         Target.Value = frm.TextBox1.Value
         Set frm = Nothing
      End If
   End If
End Sub

 

[scottishmovies]

Hi Rory,

Now I'm REALLY stuck!! I've got the code in, user form with a textbox and cancel / accept buttons. But I can't figure out how to get the "accept" to work??

I guess I've still alot to learn??

 

[RoryA]

Add this to the top of the form module:

Public Cancelled As Boolean

then your click button code would be:

Me.Cancelled = False
Me.Hide

and the cancel button would be the same but set Cancelled to True.

Then the worksheet code is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Dim frm As Object
   If Target.Cells.Count = 1 Then
      If Target.Column = 1 Then
         Set frm = New UserForm1
      ElseIf Target.Column = 2 Then
         Set frm = New UserForm2
      End If
 frm.Show
If Not frm.Cancelled Then Target.Value = frm.TextBox1.Value
Set frm = Nothing
   End If
End Sub

 

[scottishmovies]

Thanks Rory, all sorted now!

Regards,
Pat

 

[scottishmovies]

Hi again Rory,

While all was great with the form, things have changed!

Is it possible to get the userform to pick up the data that already exists in the row, the current form only allows input of new data and it would be a great benefit to load up with data already keyed.

Thanks again!

Pat