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

--
Regards,
Tom Ogilvy


"S. S." wrote in message
...
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.

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


-----Original Message-----
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.
.

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.



-----Original Message-----
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

--
Regards,
Tom Ogilvy


"S. S." wrote in
message
...
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.


.

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


-----Original Message-----
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.
.

.

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


--
Regards,
Tom Ogilvy


"S. S." wrote in message
...
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.



-----Original Message-----
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

--
Regards,
Tom Ogilvy


"S. S." wrote in
message
...
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.


.