davis1118
08-27-2018, 09:08 AM
I am setting up an excel addin file to print to PDF and backup the file in an archive folder. The addin first prompts the user with a userform to ask how they want the file to be saved,i.e. Entire Workbook or Active Sheet, PDF save name, etc. I wanted the userform to remember the previously picked options for either the individual sheet or entire workbook. To do this I create 4 named ranges and place values in the ranges for the userform to look at when it loads. The named ranges are dynamic with the Worksheet Codename + the specific range name. This seems to work most of the time. But on a few occasions, the named ranges don’t have the worksheet codename. Without the sheet codename, the range names from sheet to sheet are identical and write over each other. If I put a break in my code to check if the sheet codename is set as a variable, it displays correctly in the code, and then works perfectly after that.
The code first deletes any named range (if it exists),and deletes the value in the range. Then it creates the named range and hidesthe row. The only reason I delete any named ranges first is to make sure ifrows or columns were added, that the named ranges are always in the same place.This might not be needed, and I’m definitely open to a better way to do this. I’mjust not sure if the current method I’m using is not the best way, which is whymy range names are not being named correctly. Below is the part of the code relating to the range names. I can include more code if necessary, but it is just printing to PDF and creating footers.
On Error GoTo lbl_Error2
folderPath = Application.ActiveWorkbook.Path
myName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".") - 1))
ext = Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - InStrRev(ActiveWorkbook.Name, "."))
backupdirectory = "Archive"
T = Format(Now, "ddMmmyy")
shtnm = ActiveSheet.Name
On Error GoTo lbl_Error
frmSaveMethod.Show
Set myform = frmSaveMethod
Select Case myform.Tag
Case 1
customname = LTrim(myform.TextBox3.Value)
'*******************************
'ACTIVE SHEET
'*******************************
If myform.ActiveSheetBtn.Value = True Then
Set ws = ActiveWorkbook.ActiveSheet
codenm = ws.CodeName
arr = Array(codenm & "PrintWhat", codenm & "PdfFormat", codenm & "PdfSaveName", codenm & "CustomName")
j = 1
For i = LBound(arr) To UBound(arr)
'Delete any existing named ranges
On Error Resume Next
ws.Range(arr(i)).Value = ""
ws.Range(arr(i)).EntireRow.Hidden = False
ActiveWorkbook.Names(arr(i)).Delete
On Error GoTo lbl_Error
'Create new named ranges
ws.Range("HH45000").Offset(0, j).Value = ""
ws.Range("HH45000").Offset(0, j).Name = arr(i)
ws.Range(arr(i)).Offset(0, j).EntireRow.Hidden = True
j = j + 1
Next I
Thank you very much for the help!
The code first deletes any named range (if it exists),and deletes the value in the range. Then it creates the named range and hidesthe row. The only reason I delete any named ranges first is to make sure ifrows or columns were added, that the named ranges are always in the same place.This might not be needed, and I’m definitely open to a better way to do this. I’mjust not sure if the current method I’m using is not the best way, which is whymy range names are not being named correctly. Below is the part of the code relating to the range names. I can include more code if necessary, but it is just printing to PDF and creating footers.
On Error GoTo lbl_Error2
folderPath = Application.ActiveWorkbook.Path
myName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".") - 1))
ext = Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - InStrRev(ActiveWorkbook.Name, "."))
backupdirectory = "Archive"
T = Format(Now, "ddMmmyy")
shtnm = ActiveSheet.Name
On Error GoTo lbl_Error
frmSaveMethod.Show
Set myform = frmSaveMethod
Select Case myform.Tag
Case 1
customname = LTrim(myform.TextBox3.Value)
'*******************************
'ACTIVE SHEET
'*******************************
If myform.ActiveSheetBtn.Value = True Then
Set ws = ActiveWorkbook.ActiveSheet
codenm = ws.CodeName
arr = Array(codenm & "PrintWhat", codenm & "PdfFormat", codenm & "PdfSaveName", codenm & "CustomName")
j = 1
For i = LBound(arr) To UBound(arr)
'Delete any existing named ranges
On Error Resume Next
ws.Range(arr(i)).Value = ""
ws.Range(arr(i)).EntireRow.Hidden = False
ActiveWorkbook.Names(arr(i)).Delete
On Error GoTo lbl_Error
'Create new named ranges
ws.Range("HH45000").Offset(0, j).Value = ""
ws.Range("HH45000").Offset(0, j).Name = arr(i)
ws.Range(arr(i)).Offset(0, j).EntireRow.Hidden = True
j = j + 1
Next I
Thank you very much for the help!