PDA

View Full Version : If statement help



leal72
08-28-2009, 07:50 AM
Realizing that I'm going to kick myself when I get th answer but :banghead:

I have a Input box set up. I want to tell Excel that if no name is entered into the Input box to exit the macro and if a name is entered to go ahead and semae the workbook.

I have this and it works fine if a name is added but get an error if it is left blank and would like to clean it up.


' User Input box to name workbook
WkbName = InputBox("Name the workbook to store test data", "Workbook Name")

' Save named workbook
NewWkb.SaveAs FileName:=WkbName

Bob Phillips
08-28-2009, 09:31 AM
WkbName = InputBox("Name the workbook to store test data", "Workbook Name")
If WkbName <> "" Then

' Save named workbook
NewWkb.SaveAs Filename:=WkbName

'...
End If

leal72
08-28-2009, 09:41 AM
thank so much!

leal72
09-18-2009, 09:48 AM
I get an error if the name that is input belongs to an open workbook. I need to change WkbName, I get type mismatch error. Thinking that's because WkbName references a string and the Is Active is referencing an event. Working on how to correct it but would like some help if possible.


' Create new workbook with single sheet chart
Set NewWkb = Workbooks.Add(xlWBATWorksheet)
' User Input box to name workbook
WkbName = InputBox("Name the workbook to store test data", "WORKBOOK NAME")
' Exit if dialog box canceled
If WkbName <> "" Then
NewWkb.SaveAs FileName:=WkbName ' Save named workbook
ElseIf WkbName Is Active Then
Prompt = "This workbook is already open!"
Title = "INVALID WORKBOOK NAME!"
WkbName = InputBox(Prompt, Title)
Else
Prompt = "You did not name the workbook!"
Title = "WORKBOOK NOT NAMED!"
MsgBox Prompt, vbInformation, Title
Exit Sub
End If

mdmackillop
09-19-2009, 03:28 AM
wkbname = InputBox("Name the workbook to store test data", "WORKBOOK NAME")
' Exit if dialog box canceled
If wkbname <> "" And Not Exists(wkbname) Then


Function Exists(Nm) As Boolean
Dim wb As Workbook
If Nm = "" Then
Exists = False
Exit Function
End If
Set wb = Workbooks(Nm & ".xls")
Exists = Not wb Is Nothing
End Function

leal72
09-21-2009, 07:49 AM
I'm getting a "script out of range" error on the lineSet Wb = Workbooks(Nm & ".xls")

Bob Phillips
09-21-2009, 08:02 AM
That suggests that Nm does not refer to an open workbook.

leal72
09-21-2009, 08:18 AM
maybe I'm not understanding or I'm asking the wrong questions

in debug mode if I cursor over Nm it does equal it name that was entered in the input box

Bob Phillips
09-21-2009, 09:21 AM
But does it equal the name of an open workbook in your Excel session?

leal72
09-21-2009, 09:37 AM
if I run this, I am able to create a new workbook and name it.

Sub TestOpenMethod()
Dim NewWkb As Workbook
Dim WkbName As String
Dim FileName As Variant
' User Input box to name workbook
WkbName = InputBox("Name the workbook to store test data", "WORKBOOK NAME")

' Create new workbook with single worksheet
Set NewWkb = Workbooks.Add(xlWBATWorksheet)

' Exit if dialog box canceled
If WkbName <> "" Then
NewWkb.SaveAs FileName:=WkbName ' Save workbook with InputBox info
Else
NewWkb.Close ' Close created workbook if InputBox is cancelled
Exit Sub
End If

End Sub



if I run this

Sub TestOpenMethod()
Dim NewWkb As Workbook
Dim WkbName As String
Dim FileName As Variant
' User Input box to name workbook
WkbName = InputBox("Name the workbook to store test data", "WORKBOOK NAME")

' Create new workbook with single worksheet
Set NewWkb = Workbooks.Add(xlWBATWorksheet)

' Exit if dialog box canceled
If WkbName <> "" And Not Exists(WkbName) Then
NewWkb.SaveAs FileName:=WkbName ' Save workbook with InputBox info
Else
NewWkb.Close ' Close created workbook if InputBox is cancelled
Exit Sub
End If

End Sub

Function Exists(Nm) As Boolean
Dim Wb As Workbook
If Nm = "" Then
Exists = False
Exit Function
End If
Set Wb = Workbooks(Nm & ".xls")
Exists = Not Wb Is Nothing
End Function


I am not able to name the workbook, it opens a workbook and I get an error that keeps me from entering a name for the workbook. If I have a workbook open in my excel session with the same name as the one I entered I have the same issue with the second macro listed. Same error, same place.