Error on input box

[jamie.cutting]

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

 

[Bob Phillips]

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)

 

[Incidental]

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

 

[jamie.cutting]

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