Assign value of cell based on text box

  • Thread starter Thread starter S. S.
  • Start date Start date
S

S. S.

I currently have a button that, when clicked, will add a
new sheet to the workbook. It will also open a form with
a text box. The intent is to have the user enter text
into the text box and have it appear in cell A1 of the new
sheet. (and eventually have that value become the name of
the sheet). This does not seem to be working correctly.

Here is what I have so far:

Private Sub Cmd_NewSheet_Click()
Load Frm_PStartDate
Frm_PStartDate.Show
Worksheets.Add.Move after:=Worksheets("Start")
Worksheets(2).Range("A1").Value = Txt_PStartDate
End Sub

Private Sub Cmd_Ok1_Click()
Dim Pdate As String
Pdate = Txt_PStartDate.Value
End Sub

Any help is appreciated.

Thanks

S.
 
Private Sub Cmd_NewSheet_Click()
Dim ws as Worksheet
Load Frm_PStartDate
Frm_PStartDate.Show
set ws = Worksheets.Add(after:=Worksheets("Start"))
ws.Range("A1").Value = Txt_PStartDate
End Sub
 
Hi S.S.,

Try the following macro:
First open an InputBox. If text in InputBox is empty
nothing will happen. If there is a text in the InputBox a
new sheet will be added, the sheet gets the name from the
Input Box and cell A1 contains the text.
Regards
Klaus

Sub AddSheetAndName()
Answer = InputBox("Insert Name", , "")
If Answer = "" Then Exit Sub
Sheets.Add
ActiveSheet.Name = Answer
Cells(1, 1).Value = Answer
End Sub
 
This does not exactly work. The form does not close and
will not let me continue on with other things.

I think that when the form closes it no longer allows the
Private Sub Cmd_NewSheet_Click() function access to the
variable.

Does this make sense?

S.
 
Klaus,

Perfect!

Thanks,

S.


-----Original Message-----
Hi S.S.,

Try the following macro:
First open an InputBox. If text in InputBox is empty
nothing will happen. If there is a text in the InputBox a
new sheet will be added, the sheet gets the name from the
Input Box and cell A1 contains the text.
Regards
Klaus

Sub AddSheetAndName()
Answer = InputBox("Insert Name", , "")
If Answer = "" Then Exit Sub
Sheets.Add
ActiveSheet.Name = Answer
Cells(1, 1).Value = Answer
End Sub


.
Click to expand...
 
It works fine if you hide your form and then unload it in the macro after
you get your value:

Private Sub Cmd_NewSheet_Click()
Dim ws as Worksheet
Load Frm_PStartDate
Frm_PStartDate.Show
set ws = Worksheets.Add(after:=Worksheets("Start"))
ws.Range("A1").Value = Txt_PStartDate
UnLoad Frm_PStartDate
End Sub

The code in the form should

me.hide

rather than
unload me
 
Back
Top