Plus sign in input box causes error

[9tanstaafl9]

Hi everyone. If someone accidentally (and this has happened) enters a job number followed by a plus sign into the input box, they get a 1004 error, which effectively lets them exit the macro while the sheet is still unprotected.

I can't figure out why this is happening, since I don't unprotect the sheet until after I'm done testing to see if the number is valid. Job numbers could be any positive integer.

Private Sub ChangeJob()
'creates new job, deletes old projections
 
'tests to see if connected to data first
ActiveWorkbook.Unprotect Password:="ma$terbuilder" 'needed to set visibility
Worksheets("Cost Code Names").Visible = xlSheetVisible
Worksheets("Cost Code Names").Unprotect Password:="ma$terbuilder"
On Error Resume Next
Worksheets("Cost Code Names").Range("b1").QueryTable.Refresh BackgroundQuery:=False
If Err.Number <> 0 Then
    MsgBox ("You are not connected to your Master Builder data, try again later.")
    Worksheets("Cost Code Names").Protect Password:="ma$terbuilder"
    Worksheets("Cost Code Names").Visible = xlVeryHidden
    ActiveWorkbook.Protect Password:="ma$terbuilder"
    Exit Sub
End If
 
Dim YesNo As Variant
YesNo = MsgBox("Are you sure you want to create a new job?  If you have entered any notes or projections, you should choose NO and save any changes before creating a new job.", vbYesNo + vbCritical + vbDefaultButton2, "NEW JOB")
Select Case YesNo
 
Case vbYes
 
Start:
Dim a As Variant
a = Application.InputBox("Enter the Job Number you wish to report on.", "Enter JOB NUMBER.")
    On Error Resume Next
   If a = "" Then End
   If a = False Then End
 
   Dim xx As Variant
   xx = a
   If Not IsNumeric(xx) Or Val(xx) <> Int(xx) Then
        MsgBox "Whole numbers only please."    
        GoTo Start
   End If
 
    ActiveSheet.Unprotect Password:="ma$terbuilder"
    ActiveWindow.FreezePanes = False
 
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
 
    Range("RealJobNumber").Value = a
    'alter when adding rows or columns
    If Range("d17") = "" Then GoTo WasBlank
 
    Application.Run "RemoveAll"
 
WasBlank:
 
    Application.Run "Everything" 'processes the job
 
Case vbNo
 
End Select

And yes, I'm somewhat new to this, so if my code is wasteful or goofy, please tell me what to fix instead of just laughing.

Thanks!
Jennifer

 

[venkat1926]

off the cuff suggestion
in the data base entered by user or others, in the cell where the job number is entered introduce a validation so that even at entry stage the mistake of adding + will be avoided. read help under "data validation" in excel sheet.

 

[GTO]

Hi Jen,

Hopefully someone knowledgeable in QueryTable will stop in, as I am curious. Are you sure you need to make the sheet visible/unprotected to get the table updated?

Mark

 

[GTO]

oops... double tapped...

 

[9tanstaafl9]

Hi Jen,

Hopefully someone knowledgeable in QueryTable will stop in, as I am curious. Are you sure you need to make the sheet visible/unprotected to get the table updated?

Mark

Yes, I found that one out the hard way. It wouldn't work without the unprotect.

As for the other comment, I can't use the data validation, because I need to know it's okay before the macro continues. It's a doozy of a macro, and the users are not smart enough to handle entering the job number and then pressing a button. Good thought though...

Then again, I know KNOTHING about query tables, I just got it to work through trial and error and help from this board (thank you!).

Gotta put my little one to bed, be back soon.

Jennifer

 

[GTO]

Hi again Jennifer,

Yes, I found that one out the hard way. It wouldn't work without the unprotect...

Hi Jen,

...Are you sure you need to make the sheet visible/unprotected to get the table updated?

Sorry - I mis-worded my question. What I meant to ask was whether the sheet needed to be visible (and hence the workbook unprotected to change the sheet's visibility).

Again, I don't use queries, but I would think that we should be able to either (1) just leave the sheet unprotected all the time, but always veryhidden, or (2) unprotect/protect the sheet with it still veryhidden.

Anyways, here is what I came up with. I of course cannot test, so please try in a throwaway copy.

In a Standard Module:

Option Explicit
Const PWD As String = "ma$terbuilder"
 
'// We could use a Function to return a True/False as to whether we found   //
'// our 'connection'.                                                       //
Function Connection_IsBroken() As Boolean
    On Error Resume Next
    '// Unless the ActiveWorkbokk is supposed to be another workbook,       //
    '// ThisWorkbook always is explicit (better) to the workbook the code   //
    '// is housed in.                                                       //
    With ThisWorkbook
        '// We can use a Constant for the password, just so we don't have   //
        '// to type it ea time; plus, if you ever need to change it, easier //
        '// to change it once than find every single UnProtect/Protect...   //
        .Unprotect Password:=PWD
        With .Worksheets("Cost Code Names")
            '// I'm still unsure we need to display the sheet, but if yes,  //
            '// we'll turn off ScreenUpdating and just show it for the      //
            '// minimum we need to.                                         //
            Application.ScreenUpdating = False
 
            .Visible = xlSheetVisible
            .Unprotect Password:=PWD
            .Range("B1").QueryTable.Refresh BackgroundQuery:=False
 
            .Protect Password:=PWD
            .Visible = xlSheetVeryHidden
 
            Application.ScreenUpdating = True
        End With
        .Protect Password:=PWD
    End With
 
    If Err.Number > 0 Then
        Connection_IsBroken = True
 
        MsgBox "You are not connected to your Master Builder data," & vbCrLf & _
                "Try again later.", vbExclamation, ""
    End If
 
End Function
 
Function JobNumber() As Variant
Dim Tmp As Variant
 
'// We will use a GoTo here, so as to recurse within the procedure, til the //
'// user either gives us a good number or gives up.                         //
ReStart:
 
    '// We'll use Tmp as a variant, since it could return a Boolean (False) //
    '// if the user cancels, or a number of course.                         //
    '// As to your initial problem, we can set the Application.InputBox     //
    '// Type to 1, to insist upon a number :-)                              //
    Tmp = Application.InputBox( _
                            "Enter the Job Number you wish to report on." & _
                            String(2, vbCrLf) & _
                            "This MUST be an Integer (whole number).  There" & _
                            " cannot be a decimal point, ""+/-""" & _
                            " characters, etc.", _
                            "Enter JOB NUMBER.", , , , , , 1)
 
    '// Since we don't have to worry about the InputBox returning anything  //
    '// but a number, we'll just make sure the number is an Integer.        //
    If Not Int(Tmp) = Tmp Then
        GoTo ReStart
    Else
        JobNumber = Tmp
    End If
End Function
 
Sub Temp_ChangeJob()
Dim varJobNumber As Variant
 
    '// See Function notes                                                  //
    If Connection_IsBroken Then Exit Sub
 
    If MsgBox("Are you sure you want to create a new job?" & _
               String(2, vbCrLf) & _
              "If you have entered any notes or projections, you should" & _
              vbCrLf & _
              "choose NO and save any changes before creating a new job.", _
              vbYesNo + vbCritical + vbDefaultButton2, "NEW JOB") = vbYes Then
 
        '// Set the value of another variable to the return of JobNumber, so//
        '// we don't end up running the function additional time(s).        //
        varJobNumber = JobNumber
 
        '// If the user didn't cancel the InputBox...                       //
        If Not varJobNumber = False Then
 
            ActiveWindow.FreezePanes = False
 
            With ActiveSheet
                .Unprotect Password:=PWD
 
                If .AutoFilterMode Then .AutoFilterMode = False
 
                .Range("RealJobNumber").Value = varJobNumber
 
                If .Range("D17").Value = Empty Then
                    '// Nothing "wrong" with Run, but let's try just a      //
                    '// straight call.  As to the GoTo's, you'll want to    //
                    '// avoid these most of the time, unless an             //
                    '// intra-procedure recurse is necessary or special     //
                    '// circumstance.  Generally, very many goto's make the //
                    '// code hard to follow and easy to make mistakes in    //
                    '// (IMHO).                                             //
                    'Application.Run "Everything"
                    Call Everything
                Else
                    Call RemoveAll
                End If
            End With
        End If
    End If
End Sub

Let me know if the code commenting helps. I'm happy to include if it does, but if not, will leave out (I are a dang slow typist...).

Hope this helps

Mark

 

[mikerickson]

Using the type argument of Application.InputBox will let Excel do the data validation.

    Dim a As Long

    a = Abs(Application.InputBox("Enter the Job Number you wish to report on.", "Enter JOB NUMBER.", Type:=1))
    If a = 0 Then End: Rem cancel pressed

    Rem a is now a positive integer.

 

[9tanstaafl9]

Many thanks for all the suggestions. I have had a migraine since Saturday and am too loopy from the lack of sleep and imitrex and pain to actually test these out yet. Will check back soon.

Yes, I love the commenting. Thank you.