Repeat a tab & enter command depending on cell entry?

elitepaper

New Member
Joined
May 13, 2021
Messages
2
I'm wondering if a genius amongst MrExcel could help me out!

I've created a macro that auto populates a PDF document (essentially customer information) which works fine, but I want to go one step further and make it 'tick' a number of checkboxes depending on a cell entry.

At the moment it checks the first box absolutely fine using

Application.SendKeys "{Tab}", True
Application.SendKeys "~", True

But I essentially want it to continue checking the boxes, based on a particular cell entry (Column 'Bags Destroyed'). So for the below customer, it would continue ticking 12 boxes in total, then 5 for the next customer etc.

To give you some background, we're a charity that deals with paper shredding, and this document will be used to sign off each job (amount of bags we've collected from customer) once the paper has been shred successfully. We use this for audit trail, and have always used paper copies - finally we're looking at catching up with technology!

batch id screenshot.png

batch id auto screenshot.png


Here's the full macro;


Option Explicit

Sub PDFTemplate()
Dim PDFFldr As FileDialog
Set PDFFldr = Application.FileDialog(msoFileDialogFilePicker)
With PDFFldr
.Title = "Select PDF file to attach"
.Filters.Add "PDF Type Files", "*.pdf", 1
If .Show <> -1 Then GoTo NoSelection
Sheet1.Range("s2").Value = .SelectedItems(1)
End With
NoSelection:
End Sub
Sub SavePDFFolder()
Dim PDFFldr As FileDialog
Set PDFFldr = Application.FileDialog(msoFileDialogFolderPicker)
With PDFFldr
.Title = "Select a Folder"
If .Show <> -1 Then GoTo NoSel:
Sheet1.Range("s4").Value = .SelectedItems(1)
End With
NoSel:
End Sub

Sub CreatePDFForms()
Dim PDFTemplateFile, NewPDFName, SavePDFFolder, Company, Refno As String
Dim ApptDate As Date
Dim CustRow, LastRow As Long
With Sheet1
If .Range("s2").Value = Empty Or .Range("s4").Value = Empty Then
MsgBox "Both PDF Template and Saved PDF Locations are required for macro to run"
Exit Sub
End If

LastRow = .Range("E9999").End(xlUp).Row 'Last Row
PDFTemplateFile = .Range("s2").Value 'Template File Name
SavePDFFolder = .Range("s4").Value 'Save PDF Folder
ThisWorkbook.FollowHyperlink PDFTemplateFile
Application.Wait Now + 0.00002

For CustRow = 5 To 5
Refno = .Range("E" & CustRow).Value 'Ref No
Company = .Range("F" & CustRow).Value 'Company

Application.SendKeys "{Tab}", True
Application.SendKeys Company, True 'company
Application.Wait Now + 0.00002
Application.SendKeys "{Tab}", True

Application.SendKeys .Range("E" & CustRow).Value, True 'Ref No
Application.Wait Now + 0.00002
Application.SendKeys "{Tab}", True

Application.SendKeys .Range("o" & CustRow).Value, True 'Collection Date
Application.Wait Now + 0.00002

Application.SendKeys "{Tab 6}", True
Application.SendKeys "~", True
Application.Wait Now + 0.00002
Application.SendKeys "{Tab}", True

Application.SendKeys "+^(s)", True
Application.Wait Now + 0.00003

Application.SendKeys "keydown = 13"

Application.Wait Now + 0.00006
Application.SendKeys "%(n)", True
Application.Wait Now + 0.00003
Application.SendKeys SavePDFFolder & "\" & Company & "_" & Refno & "_" & "BATCHID" & ".pdf"

Application.Wait Now + 0.00006
Application.SendKeys "{Enter}", True
Application.Wait Now + 0.00006


Next CustRow
Application.Wait Now + 0.00004
Application.SendKeys "^(q)", True
Application.SendKeys "{tab}", True
Application.SendKeys "{Enter}", True
Application.SendKeys "{numlock}%s", True
Application.Wait Now + 0.00003

Application.Speech.Speak "BATCH ID TICKETS HAVE NOW BEEN GENERATED"

End With
End Sub



Appreciate any help on this, thanks in advance!!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,721
I haven't tested it, but see if this helps. First, within your For/Next loop, get the number of bags that were destroyed...

VBA Code:
        bagsDestroyed = .Range("N" & CustRow).Value

Then, replace...

VBA Code:
Application.SendKeys "{Tab 6}", True
Application.SendKeys "~", True

with

VBA Code:
        For i = 1 To bagsDestroyed
            With Application
                If i = 1 Then
                    .SendKeys "{Tab 6}", True
                Else
                    .SendKeys "{Tab}", True
                End If
                .SendKeys "~", True
            End With
        Next i

And, of course, declare your variables...

VBA Code:
    Dim bagsDestroyed As Long
    Dim i As Long

Hope this helps!
 

elitepaper

New Member
Joined
May 13, 2021
Messages
2
I haven't tested it, but see if this helps. First, within your For/Next loop, get the number of bags that were destroyed...

VBA Code:
        bagsDestroyed = .Range("N" & CustRow).Value

Then, replace...

VBA Code:
Application.SendKeys "{Tab 6}", True
Application.SendKeys "~", True

with

VBA Code:
        For i = 1 To bagsDestroyed
            With Application
                If i = 1 Then
                    .SendKeys "{Tab 6}", True
                Else
                    .SendKeys "{Tab}", True
                End If
                .SendKeys "~", True
            End With
        Next i

And, of course, declare your variables...

VBA Code:
    Dim bagsDestroyed As Long
    Dim i As Long

Hope this helps!


Apologies, I meant to come back to you!

This worked perfectly! The only thing I added was a quit command, so that the boxes won't get unticked with the next customer. The macro now opens, fills, ticks, saves, quits, opens a new pdf, then repeats the process until the last job.

Top man, genuinely couldn't have done that without your help!
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,721
That's great, I'm glad I could help.

And thanks for the feedback.

Cheers!
 

Forum statistics

Threads
1,141,301
Messages
5,705,580
Members
421,399
Latest member
hjweiss00

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
Top