Calling a UserForm when a user clicks a cell

[Jack Feeman]

Hi,

Please help. I have a UserForm1 when is a data input form. It works great from the VBA Editor. My problem is now:
1) I want the UserForm1 to display and take focus when the user clicks in cell B25 of the Evaluation Worksheet.
2) I need the data typed into the textbox on the UserForm1 to be transferred to that cell B25 and deleted from the Userform.

Thanks
Jack

[vonpookie]

1) I want the UserForm1 to display and take focus when the user clicks in cell B25 of the Evaluation Worksheet.
Enter this in the sheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'if selection is B25, show userform
If Target.Address = "$B$25" Then UserForm1.Show
End Sub


2) I need the data typed into the textbox on the UserForm1 to be transferred to that cell B25 and deleted from the Userform.
Assuming you have a button on the userform that you can use to confirm the entry, put this in the userform module:
Private Sub CommandButton1_Click()
Range("B25") = Me.TextBox1.Text
Unload Me
End Sub

You may need to change the object names, of course.

[Jack Feeman]

Thanks vonpookie. The only info I could find concerned calling a UF from an object, event (but not on a change in cell focus), and Workbook/sheet level.

Jack

[Jack Feeman]

The only thing that does not want to cooperate with me is the code that transfers the text in the UserForm Textbox1 to the active cell in the Worksheet. I failed to realize that the specified cell B25 was actually a merged cell comprising the range B25:H25 so once I put that into the code the call worked great, however, the transfer of input text does not occur when the user clicks the OK button in the userform.
Ref:
Worksheet: Evaluation (sheet 6) Target cell: B25:H25 (merged cells)
CommandButton 1 = Cancel button on the UserForm1
CommandBUtton 2 = OK button on the UserForm1

As vonpookie stated, I put the following code behind the UserForm:

Private Sub CommandButton2_Click()
Range("$B$25:$H$25") = Me.TextBox1.Text
Unload Me
End Sub

I did amend it with the range of the merged target cell. Neither way worked. So I think it may be someother code that may be interferring with the execution of the above code. I had other code already entered which I did not touch. Please let me know if you see anything in the codesheet for the UserForm1 that would be obstructing the successful execution of the above code. Thanks.

'Private Sub Frame1_Click()
'Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim myStr As String
myStr = Replace(Me.TextBox1.Value, vbCr, "")
'for it to be treated as text
ActiveCell.Value = "'" & myStr
Unload Me
End Sub
Private Sub CommandButton2_Click()
Range("$B$25:$H$25") = Me.TextBox1.Text
Unload Me
End Sub
Private Sub Label1_Click()
End Sub
Private Sub TextBox1_Change()
Dim myMax As Long
Dim myMsg As String

myMax = 927
myMsg = "Length: " & Len(Me.TextBox1.Value) _
& " Remaining: " & _
Application.Max(0, myMax - Len(Me.TextBox1.Value))

Me.Caption = myMsg
End Sub
Private Sub UserForm_Initialize()
With Me.TextBox1
.WordWrap = True
.MultiLine = True
.EnterKeyBehavior = True
.Value = ActiveCell.Value
End With
With Me.CommandButton1
.Caption = "Cancel"
.Cancel = True
End With
Me.CommandButton2.Caption = "Ok"
End Sub


Jack

[vonpookie]

For one thing, you have two instances of CommandButton2_Click. You can only have one. Basically, you just need to combine them.

The other subs look OK, but try this:

'OK button
Private Sub CommandButton2_Click()
Dim myStr As String
myStr = Replace(Me.TextBox1.Value, vbCr, "")
'for it to be treated as text
Sheets("Evaluation").Range("B25:H25").Value = "'" & myStr
Unload Me
End Sub

[Jack Feeman]

Thanks for the quick reply. I combined both instances of commandbutton2 into the one one. The only difference is combined that the (') remains in the formula bar and does not unload with the rest of the text. None of the input text transfers over to the target cell.

Jack

[vonpookie]

I'm not really sure what's going on there--is the merged cell formatted as text?

Maybe try using the Text property instead of value?
myStr = Replace(Me.TextBox1.Text, vbCr, "")

[Jack Feeman]

Hi vonpookie,

Everything works fine except when the usr finishes typing and clicks OK (commandbutton2) the text does not transfer over to the Worksheet. Here is the UserForm1 code:


'Private Sub Frame1_Click()
'Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim myStr As String
myStr = Replace(Me.TextBox1.Value, vbCr, "")
'for it to be treated as text
Sheets("Evaluation").Range("B25:H25").Value = "'" & myStr
Unload Me
End Sub
Private Sub Label1_Click()
End Sub
Private Sub TextBox1_Change()
Dim myMax As Long
Dim myMsg As String

myMax = 927
myMsg = "Length: " & Len(Me.TextBox1.Value) _
& " Remaining: " & _
Application.Max(0, myMax - Len(Me.TextBox1.Value))

Me.Caption = myMsg
End Sub
Private Sub UserForm_Initialize()
With Me.TextBox1
.WordWrap = True
.MultiLine = True
.EnterKeyBehavior = True
.Value = ActiveCell.Value
End With
With Me.CommandButton1
.Caption = "Cancel"
.Cancel = True
End With
Me.CommandButton2.Caption = "Ok"
End Sub


The code behind the worksheet seems to work since clicking in the merged cell (B25:H25) does bring up the userform.

Can you see anything wrong with this code?

Thanks - almost there!
Jack

[Bob Phillips]

Can you post the workbook?

[Jack Feeman]

Thanks xld,
But the size is 954kb not within the allocated size for posting. Even zipped it is 425 kb. Any ideas?
Jack

[Bob Phillips]

Try posting it to a temporary web sorage site.

[Jack Feeman]

I finally posted it to my site...here is the link to the workbook.....
So-o-o close I can taste it.
Thanks Jack
http://ampuku.sharepoint.bcentral.com/Testimonial/Perf%20Evaluation%20-%20onlineTest.xls

[Ivan F Moala]

Remove the refrence in your [ControlSource] of the Textbox1

[Jack Feeman]

Thanks Ivan,
Which instance of Textbox1 is the [ControlSource]?
Sorta new at this VBA?
Jack

[Bob Phillips]

It is a property of the Textbox control.

[Jack Feeman]

Thanks, That was it. Thanks to all of you for the input.
Jack