MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Textbox - simple question?

  Asked By: Rainhard    Date: Feb 19    Category: MS Office    Views: 1231

Im trying to write a VBA form that is taking text from fields in an
excel sheet, displaying them in the form, then letting you edit and
replace the info in the sheet...

I have been using textbox to try to do this, but can you load the
textbox with the string from the sheet, such that it can then be
displayed/modified before saving back to the cell in the sheet? as
most examples seem to be using it to simply overwrite data in the

I have tried something like this:

Private Sub Command1_Click()
Text1.Text = xlsheet.Cells(2, 1) ' row 2 col 1
Text2.Text = xlsheet.Cells(2, 2) ' row 2 col 2

TextBox1 = Text1.Text
TextBox2 = Text2.Text

End Sub

But it doesnt display anything in the textbox from the sheet when
you run the form, im new to VBA so any ideas? im stuck !



7 Answers Found

Answer #1    Answered By: Scott Anderson     Answered On: Feb 19

'you can do this
Private Sub CommandButton1_Click() 'get from sheet
TextBox1.Text = ActiveSheet.Cells(2, 1)
TextBox2.Text = ActiveSheet.Cells(2, 2)
End Sub

Private Sub CommandButton2_Click() 'put into sheet
ActiveSheet.Cells(2, 1) = TextBox1.Text
ActiveSheet.Cells(2, 2) = TextBox2.Text
End Sub

'and/or perhaps this
Private Sub UserForm_Initialize() 'get from sheet
TextBox1.Text = ActiveSheet.Cells(2, 1)
TextBox2.Text = ActiveSheet.Cells(2, 2)
End Sub

Answer #2    Answered By: Abaddon Cohen     Answered On: Feb 19

I am not sure I understand.
what is TextBox1. I use Excel 2000 and I checked in Excel 2003, I do not see
any textbox  in the forms toolbar. So I guess you are using textboxes in the
if you want to copy from the worksheet to the textbox in your user form  you
may use something like:
Me.TextBox1.Text = Worksheets(1).Cells(1, 1)
You can use this only after you load  the userform
if you want then to transfer data  from a textbox in the userform to the
worksheet you may use something like
Private Sub CommandButton1_Click()
Worksheets(1).Cells(3, 3) = Me.TextBox1.Text
Unload Me 'close the userform and return to the workbook.
End Sub

Answer #3    Answered By: Jacqueline Long     Answered On: Feb 19

i have tried these but still
end up with a blank textbox  when i try it....what could be causing
this? are there any other options that can cause it to be blank?

Answer #4    Answered By: Joe Evans     Answered On: Feb 19

Excuse me if my question  sounds strange, but did you create a userform with
two textboxes named "TextBox1" and "TextBox2". Maybe there is a typo error in
the name of the textboxes?

Answer #5    Answered By: Mable Stone     Answered On: Feb 19

Firstly, and most important, put the following at the top of every code module:

Option Explicit

This will ensure that you define all variables properly. (Excel VBA editor can
be configured to put this line in automatically. In Excel 2000, the option you
need to set is "Require variable declaration".)

You don't say where you're putting your code. Assuming you have created a new
form, this code needs to go into the code module for that form. As such,
Text1.Text will refer correctly to a text  box called Text1 on your form.

xlsheet.Cells(2, 1) is a reference to cell(2, 1) of a sheet  that is pointed to
by variable xlsheet. You will need to have defined and set xlsheet to point to
the sheet you want, presumably the active sheet. Easier is just to use
ActiveSheet.Cells(2,1). From memory, Cells(2,1) will default to the active
sheet anyway.

You don't say what errors you are getting when you run  your code. Are you
getting any?

Answer #6    Answered By: Guillermo Cox     Answered On: Feb 19

I assume your code module is small. Post the lot.

Answer #7    Answered By: Anat Massri     Answered On: Feb 19

For doing the same write  this code in your form  intialize event (or anywhere
else you wish)

Text1.Text = Sheets("Sheet1").Cells(1, 1).Text

and vice versa you may modify this value and put it back  to excel sheet

Sheets("Sheet1").Cells(1, 1).Value = Text1.Text

Didn't find what you were looking for? Find more on Textbox - simple question? Or get search suggestion and latest updates.