tlc53
Active Member
- Joined
- Jul 26, 2018
- Messages
- 399
Hi,
I'm wondering if there's an easier way to do this, which is user friendly and can cope with new accounts being added. Currently I have the below code, which works great. However, I have 100 accounts and the below code is for just 3 accounts. It is going to be very time consuming writing all that code and then if a new account is added, I would need to add new code for it.
This is how it currently works.
I have a sheet called "Dashboard". From here, you can automatically save each individual account by checking a command button (hence where below code comes in). There are approximately 100 sheets which relate to each account. I have named these in the format letter-letter-letter-number-number eg. ABC01
Currently the sheet/account name is hardwired into the code. Is it possible for it to recognise the sheet name by a cell reference on the "Dashboard"? This is located in column A.
Also, the ActiveX command button is located on the same row (column J) it needs to obtain all it's information from.
The first one for eg, AAI001
A12 = AAI001 (sheet/account name)
M12 = File save path
N12 = File save name
J12 = Checkbox to run code
L12 = Cell to confirm when file saved
On my dashboard at the bottom of the table I have allocated 10 spaces for any new accounts to be added. I have pre-populated the account names from ZZZ01 to ZZZ10
Is there a more condensed way to write this code and maybe for it to understand it should get its data from the same row the command button is on (perhaps it needs to be a form control rather than an ActiveX).
Thank you!
I'm wondering if there's an easier way to do this, which is user friendly and can cope with new accounts being added. Currently I have the below code, which works great. However, I have 100 accounts and the below code is for just 3 accounts. It is going to be very time consuming writing all that code and then if a new account is added, I would need to add new code for it.
This is how it currently works.
I have a sheet called "Dashboard". From here, you can automatically save each individual account by checking a command button (hence where below code comes in). There are approximately 100 sheets which relate to each account. I have named these in the format letter-letter-letter-number-number eg. ABC01
Currently the sheet/account name is hardwired into the code. Is it possible for it to recognise the sheet name by a cell reference on the "Dashboard"? This is located in column A.
Also, the ActiveX command button is located on the same row (column J) it needs to obtain all it's information from.
The first one for eg, AAI001
A12 = AAI001 (sheet/account name)
M12 = File save path
N12 = File save name
J12 = Checkbox to run code
L12 = Cell to confirm when file saved
On my dashboard at the bottom of the table I have allocated 10 spaces for any new accounts to be added. I have pre-populated the account names from ZZZ01 to ZZZ10
Is there a more condensed way to write this code and maybe for it to understand it should get its data from the same row the command button is on (perhaps it needs to be a form control rather than an ActiveX).
Thank you!
Code:
Private Sub CheckBox1_Click()
If CheckBox1 Then
Call SavePdf1("AAI01")
CheckBox1.Value = True
End If
End Sub
Sub SavePdf1(wSheet As String)
Dim wfolder As String, wfile As String
wfolder = Range("M12").Value
wfile = Range("N12").Value
If wfolder = "" Then
MsgBox "Enter folder"
Exit Sub
End If
If wfile = "" Then
MsgBox "Enter file name"
Exit Sub
End If
If Dir(wfolder, vbDirectory) = "" Then
MsgBox "Folder does not exists"
Exit Sub
End If
If Right(wfolder, 1) <> "\" Then wfolder = wfolder & "\"
If Right(wfile, 4) <> ".pdf" Then wfile = wfile & ".pdf"
Sheets(wSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:=wfolder & wfile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
MsgBox "The file has been saved"
CheckBox1.Enabled = False
Range("L12").Value = "Saved " & Date
End Sub
Private Sub CheckBox2_Click()
If CheckBox2 Then
Call SavePdf2("ADI01")
CheckBox2.Value = True
End If
End Sub
Sub SavePdf2(wSheet As String)
Dim wfolder As String, wfile As String
wfolder = Range("M13").Value
wfile = Range("N13").Value
If wfolder = "" Then
MsgBox "Enter folder"
Exit Sub
End If
If wfile = "" Then
MsgBox "Enter file name"
Exit Sub
End If
If Dir(wfolder, vbDirectory) = "" Then
MsgBox "Folder does not exists"
Exit Sub
End If
If Right(wfolder, 1) <> "\" Then wfolder = wfolder & "\"
If Right(wfile, 4) <> ".pdf" Then wfile = wfile & ".pdf"
Sheets(wSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:=wfolder & wfile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
MsgBox "The file has been saved"
CheckBox1.Enabled = False
Range("L13").Value = "Saved " & Date
End Sub
Private Sub CheckBox3_Click()
If CheckBox3 Then
Call SavePdf3("ADI02")
CheckBox3.Value = True
End If
End Sub
Sub SavePdf3(wSheet As String)
Dim wfolder As String, wfile As String
wfolder = Rangen("M14").Value
wfile = Range("N14").Value
If wfolder = "" Then
MsgBox "Enter folder"
Exit Sub
End If
If wfile = "" Then
MsgBox "Enter file name"
Exit Sub
End If
If Dir(wfolder, vbDirectory) = "" Then
MsgBox "Folder does not exists"
Exit Sub
End If
If Right(wfolder, 1) <> "\" Then wfolder = wfolder & "\"
If Right(wfile, 4) <> ".pdf" Then wfile = wfile & ".pdf"
Sheets(wSheet).ExportAsFixedFormat Type:=xlTypePDF, Filename:=wfolder & wfile, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
MsgBox "The file has been saved"
CheckBox1.Enabled = False
Range("L14").Value = "Saved " & Date
End Sub
