Loop the same procedure until user interference

[iqbal88]

Hello Experts,

I new in VBA, I need your help.
How do I loop the procedure/macro until I cancel the InputBox or remain empty value InputBox??

Sub FundData()

Dim TikerName As String


    TikerName = Application.InputBox(Prompt:="Please enter Share Code", Type:=2)
    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://dsebd.org/displayCompany.php?name=" & TikerName, Destination:=Range( _
        "$A$1"))
        '.CommandType = 0
        .Name = "displayCompany.php?name=" & TikerName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """company"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.Name = TikerName
    Sheets.Add After:=ActiveSheet
    ThisWorkbook.Save
    
End Sub

TIA,

 

[BKrukowski]

The code should not continue until you dismiss the input box. What is happening when you run your code?

 

[iqbal88]

Thanks for your quick reply

One thing to remember, there have no loop condition in my code. But I need to loop the procedure.
And then I have to loop the procedure till InputBox ok with a correct string.
Should exit the macro, when value is empty or click cancel button

now if I cancel the InputBox, show me an error message like,

Runtime error '1004'
Application-defined or object-defined error

and also create a new sheet named "FALSE", with some incomplete data.

 

[BKrukowski]

Ok,

Dim TikerName As String

TikerName = "Name"     'I would use a Do Until ... Loop and use TikerName to control the continuation but to enter the loop TikerName must have a value 

Do Until TikerName=""    ' The Input returns a Null String if you hit cancel so we exit the loop if that occurs

TikerName = Application.InputBox(Prompt:="Please enter Share Code", Type:=2)  

If TikerName = "" Then                ' Because we redefine the TikerName value inside the loop we need to catch the cancel button before the rest of the loop executes 
Exit Sub
End if

'The rest of your code

Loop

 

[iqbal88]

Thanks for your reply.
When I remain blank the inputbox, and click OK, then then the Macro (loop) stop run.
But if I click cancel button in input, it show an error

Run time error '1004'
That name is already taken. Try different one

the code is

Sub FundData()

Dim TikerName As String


    TikerName = "Name"     'I would use a Do Until ... Loop and use TikerName to control the continuation but to enter the loop TikerName must have a value
    Do Until TikerName = ""  ' The Input returns a Null String if you hit cancel so we exit the loop if that occurs
    TikerName = Application.InputBox(Prompt:="Please enter Share Code", Type:=2)
    If TikerName = "" Then                ' Because we redefine the TikerName value inside the loop we need to catch the cancel button before the rest of the loop executes
    Exit Sub
    End If
    
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://dsebd.org/displayCompany.php?name=" & TikerName, Destination:=Range( _
        "$A$1"))
        '.CommandType = 0
        .Name = "displayCompany.php?name=" & TikerName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """company"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.Name = TikerName
    Sheets.Add After:=ActiveSheet
    ThisWorkbook.Save
    Loop
    
End Sub

 

[BKrukowski]

If TikerName = "" or TikerName = "False" Then

 

[iqbal88]

If TikerName = "" or TikerName = "False" Then

Now show another error

Runtime Error '13'
Type mismatch

 

[BKrukowski]

Is the error on the If Statement or somewhere else?

The only thought I have left is to use InputBox instead of Application.InputBox (you would not need the or ="False" because InputBox cancel returns an empty string whereas Application.InputBox returns False when cancel is clicked

InputBox is a Function
Application.InputBox is a Method

 

[iqbal88]

In If statement

If TikerName = "" Or TikerName = False Then

if click cancel inputbox then it work, if remain blank the box & click ok;
it shows the error