Can't find my typo

Js Smith

New Member
Joined
Jul 24, 2020
Messages
44
Office Version
  1. 2010
Platform
  1. Windows
I cannot find my typo. Trying to add a new VBA case number but the InputBox does not popup. No error it just ends. I'm dyslexic, so 99% sure it a typo.
I'm able to toggle the commented line to the previous 3 option version & it works. I could use another set of eyes to find the error, please!

VBA Code:
Sub Select1BL()

Dim FileToOpen As Variant
Dim OpenBook As Workbook
    FileToOpen = Application.GetOpenFilename(Title:="Browse for your File", FileFilter:="Excel Files (*.xls*),*xls*")
    If FileToOpen <> False Then
        Set OpenBook = Application.Workbooks.Open(FileToOpen)
Else
    MsgBox "This file is already in use. Please try again later."
    Exit Sub
    End If
    Dim findSub As String
    Dim r As Range
    Dim First_Address As String
    Dim bCancelled As Boolean
    On Error GoTo ErrHandler

     Dim number As Integer

  On Error Resume Next
    Sheets("OFFICE").Select
   ActiveSheet.ShowAllData

TryAgain:
'number = Application.InputBox("Enter the corresponding number for the type of edit you wish to complete:" & vbCrLf & vbCrLf & "1=  Add a Resubmittal row" & vbCrLf & "2=  Add a PM update to an existing submittal" & vbCrLf & "3=  Add an Architect update to an existing submittal" & vbCrLf & vbCrLf & "Click Cancel to exit", "What would you like to do?", Type:=1)
 
number = Application.InputBox("Enter the corresponding number for the type of edit you wish to complete:" & vbCrLf & vbCrLf & "1=  Add a Resubmittal row" & vbCrLf & "2=  Add a PM update to an existing submittal" & vbCrLf & "3=  Add an Architect update to an existing submittal" & vbCrLf & "4=  Mark an existing submittal as distributed" & vbCrLf & vbCrLf & "Click Cancel to exit", "What would you like to do?", Type:=1)

If number = False Then Exit Sub

Select Case number

   Case "1"
Call AddResubRowBL

   Case "2"
Call PmStatUpdateBL

    Case "3"
Call ARCHStatUpdateBL
        
       Case "4"
Call DistributedBL
        Case Else
  
End Select

Question:
 CarryOn = MsgBox("Would you like to do anything else?", vbYesNo, "Continue?")

If CarryOn = vbYes Then
GoTo TryAgain

GoTo Question
  End If
    
    Exit Sub
  
ErrHandler:

    MsgBox "An error (" & Err.Description & ") has occurred." & vbCrLf & _
           "Please try again." & vbCrLf & vbCrLf & _
           "If the error persists, please contact your manager.", vbExclamation, "Error"
  
End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
VBA Code:
 MsgBox "This file is already in use. Please try again later."
    Exit Sub
    End If
    Dim findSub As String
    Dim r As Range
    Dim First_Address As String
    Dim bCancelled As Boolean
    On Error GoTo ErrHandler

     Dim number As Integer

  ' On Error Resume Next  '<--- comment out this line then run your code again.
    Sheets("OFFICE").Select
   ActiveSheet.ShowAllData
 
Upvote 0
That doesn't help. I know the problem is here:

VBA Code:
number = Application.InputBox("Enter the corresponding number for the type of edit you wish to complete:" & vbCrLf & vbCrLf & "1=  Add a Resubmittal row" & vbCrLf & "2=  Add a CSDI PM update to an existing submittal" & vbCrLf & "3=  Add an Architect update to an existing submittal" & vbCrLf & "4=  Mark an existing submittal as distributed" & vbCrLf & vbCrLf & "Click Cancel to exit", "What would you like to do?", Type:=1)

I can comment that line then uncomment this, without any other changes, & it works as anticipated:

VBA Code:
'number = Application.InputBox("Enter the corresponding number for the type of edit you wish to complete:" & vbCrLf & vbCrLf & "1=  Add a Resubmittal row" & vbCrLf & "2=  Add a CSDI PM update to an existing submittal" & vbCrLf & "3=  Add an Architect update to an existing submittal" & vbCrLf & vbCrLf & "Click Cancel to exit", "What would you like to do?", Type:=1)

I simply cannot see whatever typo was committed. Typically it's punctuation, but for the life of me, I can't find it.
 
Upvote 0
NM, loose nut on the chair.

exceeded the 255 Char inputbox limit by adding the line. ?‍♀️

Rather an easy problem than a difficult one....
Cheers!
 
Upvote 0
Solution

Forum statistics

Threads
1,213,560
Messages
6,114,306
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top