macro to print range of IDs

kelly mort

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
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
Code: 
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
 
Sort by date Sort by votes
Trebor76 said:
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:

Code: 
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
Click to expand...

Wow! This macro is soo sweet. I love it thank you.
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Trebor76 said:
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:

Code: 
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
Click to expand...

stumac said:
Just change the For statement:

Code: 
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
Click to expand...

Thank you too for your version too. Will be using yours too in my projects
 
Upvote 0
Trebor76 said:
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:

Code: 
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
Click to expand...

What if i wanna take input of both text and numbers? For example if i have ID : wk1001. Can this be possible in the first place? If yes how?

Regards
Kelly
 
Upvote 0
What if i wanna take input of both text and numbers? For example if i have ID : wk1001. Can this be possible in the first place? If yes how?
Click to expand...

Not sure what you mean? Is it that you you just one entry like wk1001 made? If so an inputbox is probably overkill and some of the error checking in my example will have to go.
 
Upvote 0
Trebor76 said:
Not sure what you mean? Is it that you you just one entry like wk1001 made? If so an inputbox is probably overkill and some of the error checking in my example will have to go.
Click to expand...

Yes just one entry like wk1001 and the counter should still work. So like say wk1001-wk1009
 
Upvote 0
Try this which will work for numeric or alphanumeric entries:

Code: 
Option Explicit
Sub Macro1()

    Dim strMyArray() As String
    Dim dblMyNumber  As Double
    Dim strReponse   As String
    Dim i As Integer
    Dim strFrom As String
    Dim strTo   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  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, "-")
    
    'Determine 'from' value
    For i = 1 To Len(strMyArray(0))
        If IsNumeric(Mid(strMyArray(0), i, 1)) = True Then
            If strFrom = "" Then
                strFrom = Mid(strMyArray(0), i, 1)
            Else
                strFrom = strFrom & Mid(strMyArray(0), i, 1)
            End If
        End If
    Next i
    
    'Determine 'to' value
    For i = 1 To Len(strMyArray(1))
        If IsNumeric(Mid(strMyArray(1), i, 1)) = True Then
            If strTo = "" Then
                strTo = Mid(strMyArray(1), i, 1)
            Else
                strTo = strTo & Mid(strMyArray(1), i, 1)
            End If
        End If
    Next i
    
    If strFrom = "" Or strTo = "" Then
        If MsgBox("No numeric entry can be determined from one or both entries 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 = Val(strFrom) To Val(strTo)
        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

Please don't re-quote entire posts as it just creates clutter. Thanks.

Robert
 
Upvote 0
Thanks for the time , energy and everything. It is working good. Thanks very much i am still working on it. Now i am the with line
I have set G6 to a combobox value so that when i change the value in the combobox it will set that of G6. I love your code. Thanks again. When i am stucked i will report.
Regards
Kelly
 
Upvote 0
I think i have a big problem here. I need a code that can print the ID in a textbox. This should not show me any inputbox but instead to show a messagebox function with yes or no buttons. Then when vbYes clicked show msgbox"sent to printer"

So just after clicking the button it should show the msgbox fn then print if yes else exit sub.
Textbox name is Reg1
Thanks in advance.
Kelly
 
Last edited:
Upvote 0
You've lost me I'm afraid. I'm sure someone here will be able to help you though.

Good luck with it.

Robert
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Locked
  • Question Question
Print Settings VS Macro Print Settings
Replies
1
Views
101
Peter_SSs
Peter_SSs
N
  • Question Question
VBA to add title from formula to a save as name
Replies
0
Views
152
NKUNZI
N
F
  • Question Question
Printing: File settings override code
Replies
1
Views
93
Flavien
F
F
  • Question Question
Printing error. Nothing is printed
Replies
2
Views
267
Flavien
F
4
  • Question Question
VBA to Print Labels but Issues Selecting Printer
Replies
1
Views
325
rpaulson
rpaulson

Forum statistics

Threads
1,224,842
Messages
6,181,288
Members
453,030
Latest member
PG626

Share this page

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