Unhide all hidden sheets in a workbook
Suppose you have a workbook with many hidden sheets and you want to unhide them all at once. Here's some simple code to unhide all sheets in a workbook. In this case, I assume it is the ActiveWorkbook.
Welcome to the portion of my website where I share and store information or tools about Microsoft Access and Excel. Hope you find some of it interesting or helpful!
If you're totally new to using Microsoft Access, I recommend reading this article to help get you started.
If you have any questions or suggestions for the articles on the website, feel free to post in the forums. I can't promise my response will be quick, but I will try my best to respond to all forums posts.
Sincerely,
Azli
As an Amazon Associate I earn from qualifying purchases.
Suppose you have a workbook with many hidden sheets and you want to unhide them all at once. Here's some simple code to unhide all sheets in a workbook. In this case, I assume it is the ActiveWorkbook.
Often when I'm QA-ing a spreadsheet at work, I'll create a column that checks some aspects of the inputs or results. That column will have TRUE/FALSE values. Often I end up with hundreds (or thousands) of rows, and I want to check that none of them are FALSE (i.e. some error is present). For one column it's easy enough to just filter for FALSE.
If there were multiple columns, I could just count the number of cells in the range that are FALSE. But this made me think that I could use the inverse method (i.e. count the number of cells that are TRUE) to check if all cells had the same values.
The main idea is to count the number of cells that equal a certain value. In this case since we are wanting to check that all the cells have the same value, you can just check the range against the value of any of the cells within the range. In this case, we can check if count the number of cells that equal our criteria value is equal to the total number of cells.
The following table is a list of reserved words from the following two sources:
A long time ago I combined the words from both links into one table so I thought I'd share it here. This list should serve as a guide as to what NOT to name any variables, fields, controls, or objects in your Microsoft Access database file.
I think the two most common reserved words people may try to use as field or variable names are:
Here are a few VBA functions that I cooked up to replace all instances of a selected portion of string within string. I am sure that this isn't anything new but it's my take on it and it may save some people some time from having to figure it all out.
Aside from calling this in VBA Code, you can also use the VBA function in a Microsoft Access query or control on a form. The sample file demonstrates how it can be used in a spreadsheet.
ReplaceInStr() [Same function as Replace(), but I wrote this as an exercise]
Example: Replace all instances of "X" with "1"
i.e. =ReplaceInStr("X+1","X",1) = "1+1"
MultiReplaceInStr()
Example: Replace all instances of "A" with "1", "B" with "2", and "C" with "3". Must create two strings; one with the 'to-be-replaced' strings as a single string with commas separating the individual strings, and similarly one with the 'replacement strings'. The first replacement is done for the entire string first, then the second once the first is done, and so on.
i.e. =MultiReplaceInStr("A+B+C","A,B,C","1,2,3") = "1+2+3"
MultiReplaceInStrArray()
Example: Replace all instances of "A" with "1", "B" with "2", and "C" with "3". First provide a 'to-be-replaced' string, then the 'replacement string, and so on. The first replacement is done for the entire string first, then the second once the first is done, and so on.
i.e. =MultiReplaceInStrArray("A+B+C+ABC","A","now I know my ABC","A",1,"B",2,"C",3) = "now I know my 123+2+3+now I know my 12323"
CountInStr() - Used by MultiReplaceInStr()
e.g. CountInStr("q,w,e,r,t,y",",") = 5