userform information copy to a cell

[Moh]

Hi world,

can someone help me if possible?
i have created a userform with a textbox and a commandbutton on it, but
don't know how to use it.

all i need to know is a way to use it.

when i open my excel file the userform should appear. on the userform i will
type in my name in the (textbox1) and then press enter (commandbutton). once
i press the enter button it should copy the text from the textbox1 and paste
it to a cell (b2).

now is this possible or am i just wasting everyone's time?

Thankyou

 

[stevebriz]

Yes this is possible. Something like the below should work.

Put this code in ThisWorbook (under project in the VB editor)
Private Sub Workbook_Open()

UserForm1.Show ' or whatever your userform is called

End Sub

Put this in your userform module

Private Sub CommandButton1_Click()
' this is for your enter button
Sheet1.Range("B2").Value = Userform1.Textbox1.Value
End Sub

 

[Tom Ogilvy]

in the VBE, go to the project explorer and double click on the Thisworkbook
Entry in the project explorer under your workbook.

this should open the thisworkbook module
At the top of the module should be some dropdowns. In the left dropdown,
select workbook and from the right dropdown select Open
this will put the workbook open event declaration in the module

Private sub Workbook_Open()

End sub

In the procedure/event put in the code to show your userform. Assume your
userform is named Userform1


Private sub Workbook_Open()
Userform1.show
End sub

Now go to the commandbutton on the userform (in the VBE) and double click on
it to bring up the click event for the command button

Private Sub Commandbutton1_Click()

End sub

in that add code


Private Sub Commandbutton1_Click()
Activesheet.Range("B2").Value = Userform1.Textbox1.Value
Unload me
End sub

Obviously adjust all names to reflect those you are actually using.

 

[Moh]

Thankyou both,

after the userform disappears i have a msgbox that comes up, is it possible
i can put in a code to copy the typed data from the textbox to this msgbox?

this is what code i currently have:

Private Sub Workbook_Open()

Userform1.Show

MsgBox "Welcome (this is where the data should appear e.g John) this is
your personal revenue sheet. Please note before you click Quick Save that you
need to 'GO TO FILE AND SELECT SAVE AS .... Date Form 01 09 06.xls' first or
it will overwrite your old revenue data. Enjoy", , "INFORMATION"

End Sub

many thanks again

 

[Moh]

one more thing sorry.

if they dont typr anything in the textbox then is there a way an error
message pops up telling them to type in the textbox?

Thanks

 

[Tom Ogilvy]

Private Sub Commandbutton1_Click()
Activesheet.Range("B2").Value = Userform1.Textbox1.Value
Me.Hide '<== hide instead of unload
End sub


Private Sub Workbook_Open()

Userform1.Show

MsgBox "Welcome " & userform1.Textbox1.Text & _
" this is your personal revenue sheet. Please note before" & _
vbNewline & "you click Quick Save that you need to " & vbNewline & _
"'GO TO FILE AND SELECT SAVE AS .... Date Form 01 09 06.xls'" & _
vbNewline & " first or it will overwrite your old revenue data. " & _
vbNewline & "Enjoy", , "INFORMATION"

Unload Userform1
End Sub

 

[Tom Ogilvy]

Private Sub Commandbutton1_Click()
if trim(Userform1.Textbox1.Value) = "" then
msgbox "Please enter your name in the textbox"
exit sub
End if
Activesheet.Range("B2").Value = Userform1.Textbox1.Value
Unload me
End sub

 

[Moh]

Thank you so much Tom. it works.