How to take over the value of a userform-controle to a cell

[Filips Benoit]

Dear All,

I'm new at Excel-programming!

I want to show a form to input several lines of text in a cell.
I can NOT enlarge the cell.
I created a userform that shows when the cell has the focus.
BUT how do i take the value from the cell to the form-controle and back.
When opening the formcontrole must show the cell-value.
Closing the form the cell must show the form-controle-value.


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Target.Column = 9 And Target.Row > 6 Then
Frm_NOTA.Show
ActiveCell.Value = ........?????
End If
End Sub

Thanks,

Filip

 

[Nigel]

In the form code you need to put an initialise eveny to 'copy' the
activecell into the form control (in this case called TextBox1), then when
the user closes the form (presses cross in top right) the value is passed
back to the active cell and the form closes.

Put the following code into the form by going to VBA editor (Alt-F11) and
select FORM from the project explorer and right- click then choose VIEW code
and paste the folllowing.....

Private Sub UserForm_Initialize()
TextBox1.Value = ActiveCell.Value
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell.Value = TextBox1.Value
Unload Me
End Sub

 

[Filips Benoit]

Thanks³

In the form code you need to put an initialise eveny to 'copy' the
activecell into the form control (in this case called TextBox1), then when
the user closes the form (presses cross in top right) the value is passed
back to the active cell and the form closes.

Put the following code into the form by going to VBA editor (Alt-F11) and
select FORM from the project explorer and right- click then choose VIEW code
and paste the folllowing.....

Private Sub UserForm_Initialize()
TextBox1.Value = ActiveCell.Value
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell.Value = TextBox1.Value
Unload Me
End Sub

 

[Bob Phillips]

Another way

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Target.Column = 9 And Target.Row > 6 Then
Frm_NOTA.Load
Textbox1.Vakue = Target.Value
Frm_NotA.Show
Target.Value = Textbox1.Text
Un load Frm_NotA.Show
End If
End Sub

Just make sure that you hide the form in the form code, not unload it.

--

HTH

RP
(remove nothere from the email address if mailing direct)