macro to print range of IDs

[kelly mort]

Hello All,
I have this macro that i use to print in my workbook. Instead of it printing from 1 always, i want it to use an inputbox to specify where to start from and where to end at. For example when i run it, it should ask me for entry for start ID and end ID. Thanks

Sub Print()
         Dim CountIt, IdNum As Long
CountIt = Application.InputBox("How many IDs do you wanna print?", Type:=1)
For IdNum = 1 To CountIt
      With ActiveSheet
             .Range("G6").Value = IdNum
             .PrintOut
      End With
Next IdNum
End Sub

 

[stumac]

Just change the For statement:

Sub PrintIDs()
         Dim StartID As Long
         Dim EndID As Long
         Dim IdNum As Long
         
StartID = Application.InputBox("Enter ID to start printing at", Type:=1)
EndID = Application.InputBox("Enter ID to finish printing at", Type:=1)
For IdNum = StartID To EndID
      With ActiveSheet
             .Range("G6").Value = IdNum
             .Printout
      End With
Next IdNum
End Sub

 

[kelly mort]

Just change the For statement:

Sub PrintIDs()
         Dim StartID As Long
         Dim EndID As Long
         Dim IdNum As Long
         
StartID = Application.InputBox("Enter ID to start printing at", Type:=1)
EndID = Application.InputBox("Enter ID to finish printing at", Type:=1)
For IdNum = StartID To EndID
      With ActiveSheet
             .Range("G6").Value = IdNum
             .Printout
      End With
Next IdNum
End Sub

Thanks very much. Wanna run test on it will verify when through

 

[kelly mort]

Okay it it working fine. Just that when there is no input in the inputnox and i click cancel it takes it as zero(0) and sets the .Range("G6").Value = 0. I want to exit sub when the inputboxes did not take any input or when i click cancel. What should i do?

Regards
Kelly

 

[stumac]

Check the value once it has been input:

Sub PrintIDs()
         Dim StartID As Long
         Dim EndID As Long
         Dim IdNum As Long
         
StartID = Application.InputBox("Enter ID to start printing at", Type:=1)
If StartID = vbCancel Or StartID = 0 Then MsgBox "Cancelled": Exit Sub

EndID = Application.InputBox("Enter ID to finish printing at", Type:=1)
If EndID = vbCancel Or EndID = 0 Then MsgBox "Cancelled": Exit Sub

For IdNum = StartID To EndID
      With ActiveSheet
             .Range("G6").Value = IdNum
             .PrintOut
      End With
Next IdNum
End Sub

 

[kelly mort]

Okay working. And one thing what if there is no entry and i click okay? I want it show a message box prompting me to input ID.

Thanks
Kelly

 

[kelly mort]

I tried this but did not work any help?

If StartID = vbOk And StartID = 0 Then MsgBox "Cancelled": Exit Sub

 

[kelly mort]

Still need help with the vbOk button behaviour. Thanks

 

[kelly mort]

I tried this but did not work any help?

If StartID = vbOk And StartID = 0 Then MsgBox "Cancelled": Exit Sub

What should be done to get result??

 

[Trebor76]

Hi Kelly,

You only need one InputBox with a defined character (I used a dash in the following) to split the to and from values i.e. try this:

Option Explicit
Sub Macro1()

    Dim strMyArray() As String
    Dim dblMyNumber  As Double
    Dim strReponse   As String
    
MyInputBox:
    
    strReponse = InputBox("Enter the ID's to print from and to like so 1-20 for ID's 1 to 20:", "ID Range Selection")
    
    'Quit if the <Cancel> button has been pressed or no entry was made
    If Len(strReponse) = 0 Then
        Exit Sub
    'Ensure the reponse has a dash in it
    ElseIf InStr(strReponse, "-") = 0 Then
        If MsgBox("A valid entry like 1-20 was not made." & vbNewLine & "Try again?", vbYesNo + vbCritical) = vbYes Then
            GoTo MyInputBox
        Else
            Exit Sub
        End If
    End If
    
    strMyArray() = Split(strReponse, "-")
    
    'Ensure both to and from are numeric
    If IsNumeric(strMyArray(0)) = False Or IsNumeric(strMyArray(1)) = False Then
        If MsgBox("A valid entry like 1-20 was not made." & vbNewLine & "Try again?", vbYesNo + vbCritical) = vbYes Then
            GoTo MyInputBox
        Else
            Exit Sub
        End If
    'Ensure both to and from are positive
    ElseIf Val(strMyArray(0)) <= 0 Or Val(strMyArray(1)) <= 0 Then
        If MsgBox("A valid entry like 1-20 was not made." & vbNewLine & "Try again?", vbYesNo + vbCritical) = vbYes Then
            GoTo MyInputBox
        Else
            Exit Sub
        End If
    End If
    
    'If we get here print out the selected ID's onre-by-one
    Application.ScreenUpdating = False
    
    For dblMyNumber = strMyArray(0) To strMyArray(1)
        With ActiveSheet
            .Range("G6").Value = Val(dblMyNumber)
            .PrintOut
        End With
    Next dblMyNumber
    
    Application.ScreenUpdating = True
    
    MsgBox "ID's have now been printed.", vbInformation

End Sub

I've made some notes along the way to show what's happening should you need to tweak the macro.

Regards,

Robert