Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
number input error | Excel Discussion (Misc queries) | |||
error in input | Excel Programming | |||
Code error with input box | Excel Programming | |||
Formula input error help | Excel Discussion (Misc queries) | |||
Input Box error when scrolling | Excel Programming |