choosing worksheets in VB code?
Posted by Ben on April 17, 2000 3:50 AM
In a VB procedure, I want to be able to activate a worksheet amongst about 10 in the workbook which is usually called "Vendor Total Bef Exch", but may be called anything along those lines eg: "vendor tot bef exch"; "Vendor Total Before Exchange" etc. Capitalisation isn't a problem, but the slight differences in names means I can't refer to it directly I think.
I have done a small form which lists all the worksheets in the book for the user to select, but was wondering if there was a better way using string comparisons that anyone could think of.
Ben
Posted by Tom Morales on April 17, 2000 11:38 AM
Ben - Consider using the code name of the sheet in your code, in lieu of the sheet's given Christian name. You'll find the code names in the visual basic editor VBA Project List. The code names are Sheet1, Sheet2, etc. They remain constant irrespective of the current day's whim on sheet naming. You'll still need some discipline in your sheet use, but your code will be easier to write.
Tom
Posted by Ben on April 19, 2000 4:25 AM
What the problem is, is that every month say, a workbook is created by someone in the company with any number of worksheets. One of them will be named "blah blah" or something similar but it may have been produced in any order (and therefore not have a consistent code name each month). So I would like to work out how to 'guess' the name through some of it's unique character strings. I think the InStr function is the way to go possibly, but it's looking a bit complex and not quite foolproof.
Any ideas anyone?
Ben
