Consulting

Results 1 to 5 of 5

Thread: Excel VBA problem on application.inputbox that returns range

  1. #1

    Excel VBA problem on application.inputbox that returns range

    This code puts formula in specified range in excel by asking users for formula input by using input box and also for range to put formula in excel by using input box that returns range.
    This works fine if we specify range and formula but as if we use cancel it delivers false it gave an error I did a web search for help.
    I got a solution that is using result of range input box in variant variable and then using "if type of v as range" as in code.
    But v returns empty so I can't set Formulacell range variable as v variant.so this code fails as it exit's sub as in code.
    Please give a solution even instead of using variant variable
    sub WoVbaAppInputbox
    
    Dim Myformula as string
    Dim Formulacell as range
    Dim v as variant
    
    Myformula=Application.inputbox(prompt:="Enter formula", _Default:="=SUM(", _Type:=0)
    
    V = Application.inputbox(prompt:="specify range", _Type:=8)
    
    If Not type of v is Range then
    Exit sub
    Else
    Set Formulacell = v
    End if
    
    Formulacell.formulalocal= Myformula
    
    End sub

  2. #2
    Type 8 returns a range reference so you need to use "Set V =" I added a bit of error handler too.

    Option Explicit
    
    
    Sub WoVbaAppInputbox()
    
    
        Dim Myformula As String
        Dim Formulacell As Range
        Dim v As Range
        Dim TryAgain As VbMsgBoxResult
        
        Myformula = Application.InputBox(prompt:="Enter formula", Default:="=SUM(", Type:=0)
        If Myformula = "False" Then Exit Sub
        
    SelectAgain:
        On Error Resume Next
        Set v = Application.InputBox(prompt:="specify range", Type:=8)
    
    
        If Err.Number = 13 Then
            TryAgain = MsgBox("you didn't select a range, do you wish to select again?", vbYesNo + vbQuestion)
            If TryAgain = vbYes Then
                GoTo SelectAgain
            Else
                MsgBox "You do not wish to continue the code will now exit", vbInformation
                Exit Sub
            End If
        End If
    
    
         v.Formula = Myformula
    
    
    End Sub

  3. #3
    But the code I found in stackoverflow uses v as variant and when asked they seemed to be correct they also explained
    Here is link
    https://stackoverflow.com/questions/...50211#62550211

  4. #4
    There are multiple ways to do the same thing, I prefer using range, because most of the time I need the help of intellisense and have to use the variable in different part of the code, although I could set my range variable equal to v later, but I prefer to operate differently.

  5. #5
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,777
    I like this formulation

    Dim uiRange as Range
    
    On Error Resume Next
    Set uiRange = Application.InputBox("select range", type:=8)
    On Error Goto O
    
    If uiRange Is Nothing then Exit Sub: Rem cancel pressed
    
    Set FormulaCell = uiRange

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •