Naming multiple sheets in Code

[Nick Vittum]

(1) Why does this bit of ponderous code work—

Code: Select all

Sub ProtectMonths()
    Worksheets("Jan").Protect
    Worksheets("Feb").Protect
    Worksheets("Mar").Protect
    Worksheets("Apr").Protect
    Worksheets("May").Protect
    Worksheets("Jun").Protect
    Worksheets("Jul").Protect
    Worksheets("Aug").Protect
    Worksheets("Sep").Protect
    Worksheets("Oct").Protect
    Worksheets("Nov").Protect
    Worksheets("Dec").Protect
End Sub

—but the code below (which so many different source on the Net say should work) just doesn't?

Code: Select all

Sub ProtectMonths()
   Worksheets("Jan","Feb","Mar","Apr","May","Jun", _
         "Jul","Aug","Sep","Oct","Nov","Dec").Protect
End Sub

Assuming I haven't just done a typo or something, and the second method really isn't possible, what would be a cleaner/simpler way of accomplishing what I'm attempting here?

[HansV]

You could use

Code: Select all

Sub ProtectMonths()
    Dim i As Long
    For i = 1 To 12
        Worksheets(MonthName(i, True)).Protect
    Next i
End Sub

[Nick Vittum]

That's wonderfully short and concise. But I don't understand how it works.

1. Does Excel automatically recognize these three-letter abbreviations as MonthNames?
2. what does i do, here?

[HansV]

The MonthName function returns the full or abbreviated name of the n-th month of the year.
For example, MonthName(8, False) returns "August" and MonthName(8, True) returns "Aug".

We let i loop from 1 to 12 (the number of months in the year), and MonthName(i, True) returns the abbreviated name of the corresponding month, i.e. Jan ... Dec.

[Nick Vittum]

Wow. Interesting. It's much faster than that long version I posted, too. I assume because it's doing the whole thing at once, instead of repeating 12 times.

Okay. Thank you.

[HansV]

It does protect each sheet individually.

[snb]

Or use Excel's builtin options:

Code: Select all

Sub M_snb()
   Sheets(Application.GetCustomListContents(3)).Select
End Sub

See: https://www.snb-vba.eu/VBA_Excel_customlist_en.html" onclick="window.open(this.href);return false;

[Nick Vittum]

Or use Excel's builtin options:

Code: Select all

Sub M_snb()
   Sheets(Application.GetCustomListContents(3)).Select
End Sub

See: https://www.snb-vba.eu/VBA_Excel_customlist_en.html" onclick="window.open(this.href);return false;

Well. This is exactly what I spent two hours searching for on the Net, before I posted. The path indicated on your site to find these lists doesn't work (at least for version 365). I did finally find the lists you mention at file>Options>Advanced>Edit Custom Lists. I'm assuming these are the same lists. But no hint that they could be summoned up by the code you have above. Does one know to use (3) to refer to them simply because the format I used is third in the list of lists? And if I were to create my own custom lists, could I use the same method, using (5), (6), (whatever), to refer to them?

Thanks.

[snb]

Code: Select all

Sub M_snb()
   For j = 1 To Application.CustomListCount
       MsgBox Join(Application.GetCustomListContents(j), vbLf)
    Next
End Sub

[Nick Vittum]

@snb: ummm.... that's an interesting lesson in ways of writing a message box that I knew nothing about. But I confess, I'm completely in the dark as to how it answers my question. What does "vbLf" represent here? Perhaps that's my clue.

[snb]

Your questions are answered in the webpage I linked you to.

[Doc.AElstein]

......What does "vbLf" represent here? Perhaps that's my clue.

Don’t be side tracked by the "vbLf". Its use here is just part of the interesting way to use a message box. ( I also picked that up from snb, as I recall ).
The answer is at the web site, but you will probably need to get a bit familiar first with string functions like Split and Join, so that you don’t get side tracked by them.
( Those 2 things also play a part often in the interesting way to use a message box, ( at least if you try to use compact clever "snb" type coding ) )

Unfortunately as you try to learn VBA you get side tracked in infinite directions because there is so much to learn.

Just a bit off topic to the "vbLf"
As far as I know, a long time ago we had simple computers and a lot of big printers laying around. Inside the string that was sent to the printer we often had a couple of extra “invisible” characters to make the next text bit start on a new line: . One returned the typing carriage bit back to the left/start, & the other clicked the spindle up to feed a line of paper.

Those two characters got kept for compatibility for indicating a new line, generally, in text.
In VBA we usually represent the two things as vbCr & vbLf ... ( "vb carriage return" & "vb line feed" )

But over the years the convention used has got a bit inconsistent.
Sometimes, for a new line, a vbCr & vbLf is used , sometimes vbCr , sometimes vbLf. ( Don’t be fooled into thinking that there is as forth or fifth , like vbNewLine or vbCrLf– those things exist, but as far as I can tell they are both seen / interpreted as just the 2 characters, vbCr & vbLf )
( vbNewLine or vbCrLf = 2 characters, vbCr & vbLf )
vbLf is most often used conventionally in Excel, in cells for example to split a line – try this: write a bit of text in a cell , click in the middle of the text, hit Alt+Enter . The text gets split into two lines ( you might have to adjust the cell height to see the two lines ) . If you were to now analyse the composition of that string text in the cell, you would find that a single vbLf character has been added at the point you clicked on before doing the Alt+Enter

Often you can use either of the three ways. Either will do the same in a cell, the immediate window, or in a message box – try it on snb’s example – I think it makes no difference to the results

You occasionally see quirks and awkward compatibility problems arising due to the different conventions used. The result is that lines get joined up or you get extra unwanted empty lines appearing unexpectedly when , for example copying and pasting text from one place to another.

Alan
Ref
String stuff: https://www.excelforum.com/word-program ... ost4590792" onclick="window.open(this.href);return false;
https://docs.microsoft.com/en-us/dotnet ... -functions" onclick="window.open(this.href);return false;
vbCr VbLf: http://eileenslounge.com/viewtopic.php?f=18&t=33834" onclick="window.open(this.href);return false;
http://www.excelfox.com/forum/showthrea ... f-a-string" onclick="window.open(this.href);return false;
https://excelribbon.tips.net/T010741_Re ... paces.html" onclick="window.open(this.href);return false; ( Comments : 2020-02-24 09:17:40 , 2020-02-26 07:32:03 )

[rory]

Just FYI, there are two reasons why your original code wouldn't work. Firstly, you need an array to refer to an array of worksheets, not just a comma separated list:

Code: Select all

Worksheets(Array("Jan","Feb","Mar","Apr","May","Jun", _
         "Jul","Aug","Sep","Oct","Nov","Dec"))

Secondly, and more importantly, the Worksheets/Sheets collections don't have a Protect method, so you need to loop.

[Nick Vittum]

Unfortunately as you try to learn VBA you get side tracked in infinite directions because there is so much to learn.

Yes, I've seen that. That seems to be both the misfortune and the pleasure of it. And from what I've seen so far, it appears to me to be a bit like the English language—disorderly, often irrational, governed sometimes by arcane rules and at other times by pure whimsy. Perhaps as I learn more, some hidden logic will reveal itself, but that's the way it looks so far,

... a long time ago we had simple computers and a lot of big printers laying around. Inside the string that was sent to the printer we often had a couple of extra “invisible” characters to make the next text bit start on a new line: . . . in Excel, in cells for example to split a line – try this: write a bit of text in a cell , click in the middle of the text, hit Alt+Enter . The text gets split into two lines ( you might have to adjust the cell height to see the two lines ) . If you were to now analyse the composition of that string text in the cell, you would find that a single vbLf character has been added at the point you clicked on before doing the Alt+Enter

I had to try this, having used Alt+Enter for years, and never noticed. But I couldn't find the "vbLf". So then I went searching for a way to make Excel reveal hidden characters. Apparently that was an option, in Options, in earlier versions? So, another distraction. But it seems to me that maybe I've seen "vbLf" and "vbcr" in HTML? (Of which I know even less than I do of VBA)

Thanks for the information And for the links. I won't be looking at them today— the weather's nice today, and it's still winter here. I've got to go out and get firewood in. But I have this thread bookmarked, so I can get back to it easily later.

[Nick Vittum]

Just FYI, there are two reasons why your original code wouldn't work. Firstly, you need an array to refer to an array of worksheets, not just a comma separated list:

Code: Select all

Worksheets(Array("Jan","Feb","Mar","Apr","May","Jun", _
         "Jul","Aug","Sep","Oct","Nov","Dec"))

Secondly, and more importantly, the Worksheets/Sheets collections don't have a Protect method, so you need to loop.

Thanks for this, Rory. I did try using Array, among the many things I tried before I posted. But I didn't know about looping. I was actually reading about looping yesterday, in a different context. But I didn't understand what read well enough to put it to practical use. I'm happy with the code Hans gave me. But if you would—just for future reference—could you show me what looping would look like here?

[HansV]

Word has an option to display non-printing characters (press Ctrl+Shift+8); Excel doesn't.

[HansV]

Looping using an array of names:

Code: Select all

Sub ProtectMonths()
    Dim w As Worksheet
    For Each w In Worksheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))
        w.Protect
    Next w
End Sub

[Nick Vittum]

Looping using an array of names:

Code: Select all

Sub ProtectMonths()
    Dim w As Worksheet
    For Each w In Worksheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))
        w.Protect
    Next w
End Sub

Aha! I see how that works, and it also helps me better understand the concept of looping. So, in this example, Excel knows to stop the loop when the end of the array is reached, rather than looping endlessly? Or is it simply the fact that there's nothing left unprotected that breaks the loop?

[HansV]

The variable w is first set to Worksheets("Jan"), then to Worksheets("Feb"), etc., until finally to Worksheets("Dec"). The loop then ends because there are no more array elements.
In the loop, w is protected.

[Nick Vittum]

Thanks!