Understanding xlSheetVisible Values

[CMORailMan]

Big Picture: I have no previous experience with VBA at all, but I'm trying to get a program that was written for a different office in the company I work at. Its purpose is to create an online PDF of photos from a designated folder in a specific format. The user just enters some information about the photos and the numbers of each photo in the corresponding excel fields and runs the program by clicking a button on the cover sheet labeled "CREATE WEBSITE PHOTOS".

Problem: I get a "Run-time error '9': Subscript out of range" error occurring at the following line of code:

Sheets("EXPORT").Visible = xlSheetVisible

The code immediately surrounding this line (with that particular line in red) is as follows:

Sub HTML_Crossing()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim E As Integer
Dim F As Integer
Dim LPhoto As String
Dim RPhoto As String
Dim PhotoDesc As String
Dim FileLoc As String

A = 1
B = 2
C = 1
E = 2
F = 2
Let FileLoc = Worksheets("COVER").Range("D6")

MsgBox "SAVE THIS FILE BEFORE CONTINUING PROCESSING THIS INFORMATION!", vbInformation
MsgBox "MAKE SURE " & FileLoc & " CONTAINS FOLDERS MED AND ORG", vbInformation
ActiveWorkbook.Save

Sheets("EXPORT").Visible = xlSheetVisible
Sheets("CROSSING-HTML").Visible = xlSheetVisible
Sheets("CONCADINATION").Visible = xlSheetVisible

When I hover over xlSheetVisible, I see that xlSheetVisible = -1. With my incredibly limited knowledge, I think that is a problem, but I don't see where that value comes from or what it means. I know my lack of experience may make me more difficult to help, but I'm hoping someone can help get me to the next step with troubleshooting this.

Thanks,
Chris

 

[scott_n_phnx]

Generally, when you get a subscript out of range error, it is due to the name that is being looked for. Do you have a sheet called EXPORT or is the spelling different?

 

[CMORailMan]

Thank you! That makes some of the other forums I was reading a little more understandable. I either do not have a sheet called EXPORT, or I don't know how to see it. None of the sheets listed with tabs at the bottom of excel are named EXPORT and when I right click on one and select Unhide nothing in that list is named EXPORT either. Is there another way I could see that sheet if it does exist? It's not a typo because there is nothing close to it at all in spelling or meaning.

I tried commenting that line of code out but got the same thing on another line that uses the same sheet. I started going through the program more, and I can tell that sheet is pretty important. There is a line further down that says

Sheets.Add.Name = "EXPORT"

Should that be moved up? Does that even have anything to do with the actual existence of a sheet named EXPORT? If answers to those questions seem unlikely to help fix the problem, I would want anyone to spend a lot of time answering them, but they're all I can think of at this point.

 

[scott_n_phnx]

Yes, if that is further down the code, then it probably should be added earlier. Because of the placement of the <b>Sheets("EXPORT").Visible = xlSheetVisible</b> it looks for the sheet, which appears to have actually been added later on in the code.

Just to help you understand the visible properties, you can have them set to TRUE or FALSE, or xlVisible, xlHidden and xlVeryHidden. For these last three, they set differing values for the status. xlVisible and xlHidden are similar to right-clicking on the tab and selecting Hide or Unhide. xlVeryHidden makes it so that the sheet does not show up in the Unhide list, and can only be made visible by using a macro or by going into the worksheet property and changing the status.