InputBox.. and select Cancel instead of Save

[itsme2]

Will THis work properly? If not, how do I get the code to gracefully exit when Cancel is selected?
As it stands, if "Cancel" is selected, then the default Directory is outputted to the worksheet

Dim file_name As String
file_name = Dir(InputBox("My Data Files - .html Web Reports files", "DIRECTORY", "C:\Data\*.htm"))

If returnvalue = 6 Then 'USer Chooses Yes
            Application.Run"MyMacro"
        Else
            ActiveSheet.Delete
            Exit Sub
        End If

 

[itsme2]

OKay Why is ths Highlighted red? Is the Title Bad?

 

[Norie]

itsme2

Probably because you edited it, and therefore became subscribed to it.

Then again it could be just because it's your thread.

As to your actual question, I don't actually understand what you are trying to do.

I think you should probably take a look at Application.GetOpenFileName/GetSaveAsFilename instead of using an input box.

 

[itsme2]

Would you like to view the rest of the code?

What this does is retrieve the contents of a directory and places them on a worksheet.

I use the input box so that the deafult directory can be chang easily for those retrieving this information from a different location.

If the user chosses to cancel:

The filter *Html is simply ignored and the contents of the default excel working directory are posted too the worksheet(printed) regardless of the file extension...

Does that help?

 

[itsme2]

Thanks... sorry for the wrong thread thing

When I leave out the DIR on the string for the inputbox and when it executes the loop file_name it fails so I rem out that line of code and it just went into an endless loop

Private Sub getprintlist()

Dim file_name As String

file_name = InputBox("Here's the Directory Listing for", "DIRECTORY", "C:\*.ht*")

If file_name = "" Then Exit Sub

Do Until file_name = ""
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = file_name
 ' file_name = Dir
Loop
Range("A1").Select
ActiveCell.FormulaR1C1 = "Results"
Range("C4").Select
End Sub

 

[itsme2]

Okay I get it

Dim response As Integer

Dim file_name As String
file_name = Dir(InputBox("Here's the list", "DIRECTORY", "C:\*.ht*"))


response = MsgBox("Get Reports Canceled by User", vbOK)
If response = vbOK Then Exit Sub


'And then the rest of the code

That works... I get it now

Once again sorry for my ignorance on some of these matters

 

[itsme2]

Ooops that does quite work either

 

[erik.van.geit]

Hi, itsme2,

stumbling on this thread ...
is my memory correct, that you posted this somewhere else on the board, that you even posted 3 threads about the same object ? (I responded to 2 of them, I think)
please could you check out all this and tell us where you want to continue ?

as you can understand we don't like to answer "in parallel", doing some work which might already be done

best regards,
Erik

 

[itsme2]

Only posted here and one other thread and you had responded to simply remove DIR which just caused a vicous loop...

I tried the above however, no go on that either.

When the dialogue box appears a cancel is selected then the default directory is listed on the worksheet instead of just gracefully exiting the routine.

Let me post that again for you to look at... my attempts it have not been positive and as I say the simply removing DIR in the file_name string won't do it either

I will post back with the other thread in a second

Private Sub getprintlist()
Dim file_name As String
file_name = Dir(InputBox("HTML Files Reports", "DIRECTORY", "C:\HtmlFiles\*.ht*"))

Do Until file_name = ""
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = file_name
file_name = Dir
Loop

End Sub

 

[erik.van.geit]

removing DIR in the InputBox line allows to do the necessary check for CANCEL

to let your code work you need to use DIR later on

userinput = inputbox(....)
code (as provided) to check for cancel

file_name = DIR(userinput)