Error on input box
 

I have the following code that copies and renames a worksheet defined
by the user and adds a user defined value to a cell. However if the
user chooses cancel, the macro falls over. Is there a simple bit of
code I can add to get around this. The code, elegant it is not, is as
follows:

Sub NewWS()



Sheets("Temp").Select
Sheets("Temp").Copy After:=Sheets("database")
Sheets("Temp (2)").Select
Sheets("Temp (2)").Name = InputBox("Please input Sample Name for
Retest")
ActiveSheet.Range("F5").Value = InputBox("What was the Original
Concentration?")

ActiveSheet.Range("E3").Font.Bold = False

ActiveSheet.Range("E3").Value = ActiveSheet.Name

ActiveSheet.Range("E3").Characters(1, 5).Font.Bold = True
ActiveSheet.Range("E3").Select
ActiveCell.Font.Size = 14

End Sub

Thanks for any help anyone can offer on this.

Regards

Jamie

Error on input box
 

Sub NewWS()
Dim shName As Variant
Dim vOC As Variant

shName = InputBox("Please input Sample Name for Retest ")
If shName < "" Then
Sheets("Temp").Copy After:=Sheets("database")
Sheets("Temp (2)").Name = shName
vOC = InputBox("What was the Original Concentration?")
If vOC < "" Then
With Worksheets(shName)
.Range("F5").Value = vOC
With .Range("E3")
.Font.Bold = False
.Value = ActiveSheet.Name
.Characters(1, 5).Font.Bold = True
.Font.Size = 14
End With
End With
End If
End If

End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

wrote in message
oups.com...
I have the following code that copies and renames a worksheet defined
by the user and adds a user defined value to a cell. However if the
user chooses cancel, the macro falls over. Is there a simple bit of
code I can add to get around this. The code, elegant it is not, is as
follows:

Sub NewWS()



Sheets("Temp").Select
Sheets("Temp").Copy After:=Sheets("database")
Sheets("Temp (2)").Select
Sheets("Temp (2)").Name = InputBox("Please input Sample Name for
Retest")
ActiveSheet.Range("F5").Value = InputBox("What was the Original
Concentration?")

ActiveSheet.Range("E3").Font.Bold = False

ActiveSheet.Range("E3").Value = ActiveSheet.Name

ActiveSheet.Range("E3").Characters(1, 5).Font.Bold = True
ActiveSheet.Range("E3").Select
ActiveCell.Font.Size = 14

End Sub

Thanks for any help anyone can offer on this.

Regards

Jamie

Error on input box
 

Hi Jamie

you could try passing the text field of the input box to a string then
check the string to see if it holds a value

Option Explicit
Dim InputStr As String ' declare a string

Sub NewWS()

Sheets("Temp").Select
Sheets("Temp").Copy After:=Sheets("database")
Sheets("Temp (2)").Select
InputStr = InputBox("Please input Sample Name for Retest ") 'pass
the input box value to the string
If InputStr = "" Then ' check if the value is empty if so end the
sub
Exit Sub
Else ' continue with your code
Sheets("Temp (2)").Name = InpStr

ActiveSheet.Range("F5").Value = InputBox("What was the Original
Concentration?")


ActiveSheet.Range("E3").Font.Bold = False


ActiveSheet.Range("E3").Value = ActiveSheet.Name


ActiveSheet.Range("E3").Characters(1, 5).Font.Bold = True
ActiveSheet.Range("E3").Select
ActiveCell.Font.Size = 14

End If
End Sub


hope this helps

S

Error on input box
 

On Apr 12, 2:01 pm, "Incidental" wrote:
Hi Jamie

you could try passing the text field of the input box to a string then
check the string to see if it holds a value

Option Explicit
Dim InputStr As String ' declare a string

Sub NewWS()

Sheets("Temp").Select
Sheets("Temp").Copy After:=Sheets("database")
Sheets("Temp (2)").Select
InputStr = InputBox("Please input Sample Name for Retest ") 'pass
the input box value to the string
If InputStr = "" Then ' check if the value is empty if so end the
sub
Exit Sub
Else ' continue with your code
Sheets("Temp (2)").Name = InpStr

ActiveSheet.Range("F5").Value = InputBox("What was the Original
Concentration?")

ActiveSheet.Range("E3").Font.Bold = False

ActiveSheet.Range("E3").Value = ActiveSheet.Name

ActiveSheet.Range("E3").Characters(1, 5).Font.Bold = True
ActiveSheet.Range("E3").Select
ActiveCell.Font.Size = 14

End If
End Sub

hope this helps

S

Thanks both for the responses. Both work great.

Kind Regards

Jamie