Reading Userforms
 

Hi,

I created a userform in VBA which is activated by a button in Excel. O
this userform are 3 textboxes and 1 dropdown list. I've created a macr
for the ok button. The purpose is use the input of the textboxes in th
macro behind the Ok button.

My problem is that the value of the textboxes seems to be empty afte
I've clicked the OK box.

ok what I did:

Private Sub CmdOK_Click()

Sheets("Rate-table format").Copy after:=Sheets("Homepage")

NewSheetName_AfterUpdate
ServiceList_AfterUpdate

If NewSheetName.Value = "" Then
ActiveSheet.Name = ServiceName
Else
ActiveSheet.Name = RateSheetName
End If

End Sub

with:
Private Sub NewSheetName_AfterUpdate()
Dim RateSheetName As String
RateSheetName = NewSheetName.Value
End Sub

Private Sub ServiceList_AfterUpdate()
Dim ServiceName As String
ServiceName = ServiceList.Value
End Sub


NewSheetName is the name of a textbox. ServiceList is the name of th
dropdown box


Can anyone tell me how I can use the input of the textboxes?

THANKS

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Reading Userforms
 

Bijl167,

Try
Private Sub CmdOK_Click()

Sheets("Rate-table format").Copy after:=Sheets("Homepage")

If NewSheetName.Value = "" Then
ActiveSheet.Name = ServiceList.Value
Else
ActiveSheet.Name = NewSheetName.Value
End If

End Sub

However, if NewSheetName.Value ="" AND ServiceList.Value = "", this would
crash.
(You can't give a sheet a "blank" name.)

Try instead

Private Sub CmdOK_Click()

Sheets("Rate-table format").Copy after:=Sheets("Homepage")

If NewSheetName.Value < "" Then
ActiveSheet.Name = NewSheetName.Value
ElseIf: ServiceList.Value < "" Then ActiveSheet.Name = ServiceList.Value
End If

End Sub

This will create a copy of the sheet, but not rename it if there's no valid
name.
XL will call it "Copy of Rate-table format" or something similar.

To prevent the copying of the sheet if the user has left both fields blank

Private Sub CmdOK_Click()

If NewSheetName.Value < "" Or ServiceList.Value < "" Then
Sheets("Rate-table format").Copy after:=Sheets("Homepage")

If NewSheetName.Value < "" Then
ActiveSheet.Name = NewSheetName.Value
Else
ActiveSheet.Name = ServiceList.Value
End If
End If
End Sub

HTH
Henry


"Bijl167" wrote in message
...

Hi,

I created a userform in VBA which is activated by a button in Excel. On
this userform are 3 textboxes and 1 dropdown list. I've created a macro
for the ok button. The purpose is use the input of the textboxes in the
macro behind the Ok button.

My problem is that the value of the textboxes seems to be empty after
I've clicked the OK box.

ok what I did:

Private Sub CmdOK_Click()

Sheets("Rate-table format").Copy after:=Sheets("Homepage")

NewSheetName_AfterUpdate
ServiceList_AfterUpdate

If NewSheetName.Value = "" Then
ActiveSheet.Name = ServiceName
Else
ActiveSheet.Name = RateSheetName
End If

End Sub

with:
Private Sub NewSheetName_AfterUpdate()
Dim RateSheetName As String
RateSheetName = NewSheetName.Value
End Sub

Private Sub ServiceList_AfterUpdate()
Dim ServiceName As String
ServiceName = ServiceList.Value
End Sub


NewSheetName is the name of a textbox. ServiceList is the name of the
dropdown box


Can anyone tell me how I can use the input of the textboxes?

THANKS!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/