[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1448
  • Last Modified:

Excel VBA - Exit sub when input blank or cancel in inputbox

Hi all,

I have an issue with an inputbox when either user leaves blank or cancel. I tried to work a little with some if's but has so far failed miserably, please have a look and guide me in the right diretion.
Idea is that when the workbook is opened, then an inputbox ask for the date, this is then to be inserted in two cells and also be the name of the new sheet that is inserted if, and only if, a DATE is entered in the inputbox.

Brgds
Jesper
Sub GetDate() 'Ask for a date for the new report, insert a new sheet and insert the
                'date in "B4", "D77" and name sheet with date.
    Dim FormDate As Date
    
    FormDate = InputBox("Which Date to use?", "Form Date", Date)
    If FormDate = True Then
        ActiveSheet.Move After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Copy After:=Sheets(Sheets.Count)
        Range("B4") = FormDate
        Range("D77") = FormDate
        ActiveSheet.Name = FormDate
    End If
    If FormDate = False Then
        Exit Sub
    End If
End Sub

Open in new window

0
jemagnussen
Asked:
jemagnussen
  • 8
  • 5
  • 4
2 Solutions
 
jemagnussenAuthor Commented:
PS: I'm on a line that's on and off all the time at the moment, so please bear with me if I don't answer immediately.

Brgds
Jesper
0
 
jppintoCommented:
Please try like this.

jppinto
Sub GetDate() 'Ask for a date for the new report, insert a new sheet and insert the
                'date in "B4", "D77" and name sheet with date.
    Dim FormDate As Date
    
    FormDate = InputBox("Which Date to use?", "Form Date", Date)
    If FormDate <> "" Then
        ActiveSheet.Move After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Copy After:=Sheets(Sheets.Count)
        Range("B4") = FormDate
        Range("D77") = FormDate
        ActiveSheet.Name = FormDate
    Else
        Exit Sub
    End If
End Sub

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
This will take care of blanks as well as other non-dates
Sub GetDate() 'Ask for a date for the new report, insert a new sheet and insert the
                'date in "B4", "D77" and name sheet with date.
    Dim FormDate As Date
    
    FormDate = InputBox("Which Date to use?", "Form Date", Date)
    If IsDate(FormDate) Then
        ActiveSheet.Move After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Copy After:=Sheets(Sheets.Count)
        Range("B4") = FormDate
        Range("D77") = FormDate
        ActiveSheet.Name = Replace(FormDate, "/", "-")
    Else
        Exit Sub
    End If
End Sub

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jppintoCommented:
Improved version of my code...
Sub GetDate() 'Ask for a date for the new report, insert a new sheet and insert the
                'date in "B4", "D77" and name sheet with date.
    Dim FormDate As String
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = Workbooks("Book1")
    Set ws = wb.Sheets("Sheet1")

    FormDate = InputBox("Which Date to use?", "Form Date", Date)
    If FormDate <> "" Then
       ws.Move after:=wb.Sheets(wb.Sheets.Count)
       ActiveSheet.Copy after:=Sheets(Sheets.Count)
       Range("B4") = FormDate
       Range("D77") = FormDate
       ActiveSheet.Name = FormDate
    Else
        Exit Sub
    End If
End Sub

Open in new window

0
 
jemagnussenAuthor Commented:
Hi,

ssaqibh: ]Works when a date is typed in and when cancel, but not when blank, then it stops here:

FormDate = InputBox("Which Date to use?", "Form Date", Date)

jppinto: Does not work, as the code does not know the name of the workbook and previous sheet. I would like to get code where it does not matter what the filename is of the workbook and also not what the name of any sheets is in the workbook, the code names the sheet after the date typed in the inputbox.

Best regards
Jesper

PS: Would be super if I got also a condition to the inputbox that the format has to be date and dd-mm-yy and a msg box informing the user of this.
0
 
Saqib Husain, SyedEngineerCommented:
I thought it stopped when cancelled and not when blank. Anyways here is the revised code. It does not prompt for dd-mm-yy but it converts the date to this format.

Saqib
Sub GetDate() 'Ask for a date for the new report, insert a new sheet and insert the
                'date in "B4", "D77" and name sheet with date.
    Dim FormDate
    
    FormDate = InputBox("Which Date to use?", "Form Date", Date)
    If IsDate(FormDate) Then
        ActiveSheet.Move After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
        ActiveSheet.Copy After:=Sheets(Sheets.Count)
        Range("B4") = FormDate
        Range("D77") = FormDate
        ActiveSheet.Name = Replace(FormDate, "/", "-")
    Else
        Exit Sub
    End If
End Sub

Open in new window

0
 
jppintoCommented:
I've fixed the questions you posted. This version of the code will do what you want.

jppinto
Sub GetDate() 'Ask for a date for the new report, insert a new sheet and insert the
                'date in "B4", "D77" and name sheet with date.
    Dim FormDate As String
    Dim wb As Workbook
    Dim ws As Worksheet
    
    Set wb = ActiveWorkbook
    Set ws = wb.ActiveSheet

    FormDate = InputBox("Which Date to use?", "Form Date", Date)
    If FormDate <> "" And IsDate(FormDate) Then
       ws.Move after:=wb.Sheets(wb.Sheets.Count)
       ActiveSheet.Copy after:=Sheets(Sheets.Count)
       Range("B4") = FormDate
       Range("D77") = FormDate
       ActiveSheet.Name = FormDate
    Else
        MsgBox ("Enter a valid date!")
        Exit Sub
    End If
End Sub

Open in new window

0
 
jemagnussenAuthor Commented:
Thank you both, I got it working and used the suggestion from ippinto but also the solution from ssaqibh works fine.

Have a fine day both of you,

Best regards
Jesper
0
 
Saqib Husain, SyedEngineerCommented:
Can you tell me what was deficient in mine?
0
 
jemagnussenAuthor Commented:
Hi ssaqibh,

Only when you type in a wrong date like 1505-11 then then your code accepts this as a valid date. With a small modification the code from ippinto this comes up with a meassage telling the user that he/she did not input a valid date and therefore the code will exit without changes.

Best regards
Jesper
0
 
Saqib Husain, SyedEngineerCommented:
Jesper, when I add input the 1505-11 to either code they both accept it in the same way. So on that account both codes behave the same.

If you modified one of the codes why can you not do the same to the other code?

Saqib
0
 
jemagnussenAuthor Commented:
Hope it worked now with the split of points.

Best regards
Jesper
0
 
jemagnussenAuthor Commented:
I'm not sure if both got points now....or? The idea was that both should have points as asked for by ModCorlEEone.

Best regards
Jesper
0
 
jppintoCommented:
No, You only gave points to one of the solutions! Please click on the Request Attention link and ask to split the points...
0
 
jemagnussenAuthor Commented:
Request Attention link....??? Any clues as to where anyone would find this link.... just as a guide to others.
0
 
Saqib Husain, SyedEngineerCommented:
Thanks Modalot for the effort.

Thanks Jesper for understanding.

Saqib
0
 
jemagnussenAuthor Commented:
No problem Saqib - my pleasure, you have helped me more than ones.

Best regards
Jesper
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now