Видео

Glad you enjoyed it!

Sure Robert! Here's the link to the article with full VBA code: www.launchexcel.com/list-all-sheets-in-excel-formula-and-vba Enjoy watching the video again 😄

I agree! I think this would be very helpful as an accessibility feature if built into Excel.

Thanks for your feedback! I’m sorry for any confusion caused by the switch to color constants. Let’s break it down a bit more: When we talk about color constants in VBA, we’re referring to predefined values that represent colors. For example, vbRed, vbGreen, and vbBlue are constants for red, green, and blue, respectively. These constants make it easier to set colors without needing to know the exact RGB values. Here’s a quick explanation of what I did: 1. Color Constants: These are used to make your code more readable. Instead of typing the RGB values every time you need a color, you can use these constants. 2. Setting Cell Colors: When I set the cell colors using these constants, it was to visually differentiate parts of the master sheet for better clarity and organization. Additionally, you can define your own color constants by assigning RGB values to them. This can make your code even more readable and tailored to your needs. Here’s a simple example: Const myCustomColor As Long = RGB(121, 0, 121) ' This defines a custom purple color Now, you can use myCustomColor in your code to apply this specific color.

Unfortunately, Excel doesn't have this as a one-click feature. Hence this long answer.

Thank you for your kind words! I love the building on process with levels. TBH it's a bit difficult to plan and implement but it really helps with learning. So I'll try to do more videos like this!

You're welcome!

Do you mean your file is protected? And you can't use this method?

No problem 👍 glad you like it!

Thanks and well spotted! Yes, in the Level 1 comment I wrote celll("row") instead of cell("row")... you're right it should be cell with 2 Ls not 3.

Glad it was helpful!

Thank you so much @Wreinie! 🥳 Let’s spread the word together and build this awesome community. Stay tuned for more fun and learning! 🚀✨

Thank you for your kind words! So glad you loved the skill levels. Keep upping your game! 🎉

Thanks @jayeshkmodi!

How about you check out JKP's addin called "Follow cell pointer". It does a similar job using arrows instead of highlighting the row and column. jkp-ads.com/download.asp#followcellpointer JKP's Excel knowledge is very deep, and he has many interesting downloads. If you end up using the addin, let us know how it goes!

Dear Carel, Your formulas look correct, so i'm not sure why you are getting an error message. I have two suggestions for you: 1. Go through my article step-by-step and make sure you have followed everything exactly: www.launchexcel.com/highlight-active-row-column-excel-7-levels 2. Ask Excel Rescue to give you some help. Excel Rescue is a done-for-you small tasks service for Excel, Access, and Google Sheets. A very professional team that I highly recommend. You can reach them here: go.launchexcel.com/excel-rescue-main

@@launchexcel thanks Victor for your swift reply; will I did follow the steps carefully but somehow these 2 formula's don't seem to work. Anyway, I'll try you other suggestion. Thanks again! - rg. Carel

You're welcome 😊

Glad you liked it!!

hi @eduartopimentel I suggest you try enabling Excel 4.0 macros. That might fix the issue. Here is Microsoft's support page: support.microsoft.com/en-gb/office/working-with-excel-4-0-macros-ba8924d4-e157-4bb2-8d76-2c07ff02e0b8 ➡️ Steps: 1. Click the File tab, and then click Options. 2. Click Trust Center, and then click Trust Center Settings. 3. Click Macro Settings, and then select the Enable Excel 4.0 macros when VBA macros are enabled check box. You can then select Disable VBA macros with notification or Enable all macros (not recommended; potentially dangerous code can run). 4. Click File Block Settings and then, under File Type, select Excel 4 MacroSheets and make sure that the check box for Open is selected. 5. Under Open behavior for selected file types, click Open selected file types in Protected View and allow editing. 6. Click OK twice.

Glad you liked it!

Yes ALT is great for aligning objects to the grid. There are hundreds of these little features, the more you use Excel and watch videos like this, the more you'll pick up.

Glad you liked the video and sample file!

You are welcome!

Hi Joy! My first thought is that when you open the file, even though it’s saved as an Excel macro-enabled file, you might not have allowed macros to run. To ensure the code works, you need to enable macros. Here’s how to do it: 1. Go to File > Options > Trust Center > Trust Center Settings > Macro Settings. 2. Select Enable all macros. For detailed steps, check out my article on enabling macros here: www.launchexcel.com/enable-disable-macros-excel I'd also check that the VBA code and conditional formatting rules are still in place. If issues persist, try removing and reapplying the rules. Feel free to reach out if you need more help!

@@launchexcel Thank you so much for your kind response. I’m gonna try out and hopefully it works again. Thank you

You're welcome 😄

Thank you so much for your kind words, @mylenevelez770! 😊

Thanks again for your positive comment 😄 I recommend you check out Randy Austin for his Excel Inventory Management System. I don't have anything like that. It's on the Excel for Freelancers channel: ruclips.net/video/Mh9cHoXaBug/видео.html

Thanks for you support paramveerssachdeva! I just found an Excel add-in by JKP, who is an Excel MVP. It accomplishes the same task but in a different way, using arrow shapes drawn on the worksheet. And it works in any open workbook after you install the add-in. No need to write more code. Here's the link to JKP's Follow Cell Pointer addin: jkp-ads.com/download.asp#followcellpointer Microsoft Instructions on how to install or remove Excel add-ins: support.microsoft.com/en-gb/office/add-or-remove-add-ins-in-excel-0af570c4-5cf3-4fa9-9b88-403625a0b460#OfficeVersion=Windows P.S. As always, be careful of what you download. I can recommend JKP because of his long-standing solid reputation in the Excel community. But do be careful what you install.

Thanks for sharing the PT-BR shortcut 😄

Thanks for your comment! yes, putting this in the personal macro workbook would make it available in any workbook. However, I just found an Excel add-in that highlights the active cell either with arrows or by selecting the active row and column. It comes from from JKP (Jan Karel Pieterse) who is an Excel MVP and is a free download. I recommend you check it out. It's called "Follow Cell Pointer" and you can download it here: jkp-ads.com/download.asp#followcellpointer And here are instructions from Microsoft for adding or removing add-ins: support.microsoft.com/en-gb/office/add-or-remove-add-ins-in-excel-0af570c4-5cf3-4fa9-9b88-403625a0b460#OfficeVersion=Windows

Thanks for your comment and for watching my tutorial on highlighting the active row and column. I'd like to help, however I don't understand what you mean by "the Formula its self.help". Which part of the video did you get stuck at? If you can give me a video timestamp (mm:ss) it will give me more context to answer.

If this is an error with get.workbook(1), you might need to check if you allowed macros to work. *Q&A on listing sheet names coming up as block:* answers.microsoft.com/en-us/msoffice/forum/all/excel-list-sheet-names-coming-up-as-blocked/2167f4df-bff2-470b-96be-3797267a9195 *Microsoft page on how to correct a **#BLOCKED**! error:* support.microsoft.com/en-us/office/how-to-correct-a-blocked-error-13be117b-92e4-400a-a215-aa59d37d6e7c

Thanks @ivancortinas5427!

Thanks for the idea! Do you have code you can share to show how you implemented "Me.CodeName"?

@@launchexcel Your code: ' Must include 'MasterSheet' sheet codename so other sheets can find the macro ws.Shapes("btn_GoToMasterSheet").OnAction = "MasterSheet.Go_To_Master_Sheet" Replace it with the new suggestion: ' Must include 'MasterSheet' sheet codename so other sheets can find the macro ws.Shapes("btn_GoToMasterSheet").OnAction = Me.CodeName & ".Go_To_Master_Sheet" This should work.

@launchexcel Your code: ' Must include 'MasterSheet' sheet codename so other sheets can find the macro ws.Shapes("btn_GoToMasterSheet").OnAction = "MasterSheet.Go_To_Master_Sheet" My suggestion: ' Must include 'MasterSheet' sheet codename so other sheets can find the macro ws.Shapes("btn_GoToMasterSheet").OnAction = Me.CodeName & ".Go_To_Master_Sheet"

Thank for your question. Unfortunately, Excel doesn't provide built-in shortcuts specifically for jumping directly to the first or last sheet in a workbook. The closest I can think of is Level 2, CTRL + click on the navigation arrows to scroll to the first or last sheet. Then select the sheet. But you might also use named ranges or create simple VBA macros if you want to use the keyboard instead of mouse to jump to the first and last sheets. *--- Method 1--- Named ranges* You can manually assign named ranges like bookmarks, then use them to navigate. 1. Select the Sheet: Go to the first sheet in your Excel workbook that you want to be able to quickly access. 2. Define a Named Range: Go to the Formulas tab, then click on 'Name Manager' and choose 'New'. Name this range something recognizable like "FirstSheet". 3. In the 'Refers to' field, make sure it points to a cell in the first sheet (typically $A$1). 4. Repeat for the Last Sheet: Do the same for the last sheet in your workbook, naming the range something like "LastSheet". To jump to the first sheet, press F5 to open the Go To dialog box. Then type "firstsheet"' in the reference field. Or click on "FirstSheet" in the list of locations. And hit enter. To jump to the last sheet, press F5 to open the Go To dialog box. Then type "lastsheet"' in the reference field. Or click on "LastSheet" in the list of locations. And hit enter. Or you can directly click on the "Name Box" to the left of the formula bar and click on "FirstSheet" or "LastSheet" to navigate there. *--- Method 2--- VBA* For a more automated solution, you can use VBA to create a shortcut. Here’s a basic example of VBA code to jump to the first or last sheet. Sub GoToFirstSheet() ActiveWorkbook.Sheets(1).Activate End Sub Sub GoToLastSheet() ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count).Activate End Sub To use these macros: 1. Open Excel and press Alt + F11 to open the VBA editor. 2. Insert a new module via Insert > Module. Or use a module in your personal macro workbook. 3. Paste the above code into the module. You can then assign these macros to a keyboard shortcut: 1. Go back to Excel and press Alt + F8 to open the "Macro" dialog box. 2. You'll see your macros listed there (GoToFirstSheet and GoToLastSheet). 3. Select the first macro. 4. Click on Options. 5. Assign a keyboard shortcut and click OK. 6. Repeat 3 - 5 for the second macro. Note: If you create macros in your personal macro workbook, they will be available whenever you have Excel open. So you don't have to create the same macro for every workbook. If you don't use your personal macro workbook, you'll have to add these macros to every workbook in which you want to jump to the first and last sheet. For further instructions on how to use your personal macro workbook check out: www.launchexcel.com/excel-personal-macro-workbook

Try this. Go to "Conditional Formatting" → "Manage Rules" and then specify where you want the rule to apply to in the column called "Applies To'. You can select applies to column A:A for the row highlight and applies to row 1:1 for the column highlight. Hope that helps.

That's a great question, I think you might have to experiment with the precedence of Conditional Formatting rules. Check out this Tech Republic article as a starting point: www.techrepublic.com/article/learn-how-excel-applies-conditional-formatting-rules-to-avoid-unintended-results/

I'm glad you found it clear and concise. I love shortcuts for speeding up work in Excel. Thank you for the kind feedback!

@@launchexcel this was a really helpful tip for me, because I use two screens and if I just highlight the row that I am wondering from, the highlight disappears when the sheet loses focus. Using your method I can keep the highlight while working in another screen. Thanks 🙏

Glad you found this helpful!

Thanks for the question. I think get data from clipboard / picture works for Excel with an Office 365 subscription, sorry it doesn't work for Excel 2016. For those who use Excel 365, you also need to make sure you go to Excel > Preferences > Privacy and make sure optional connected experiences is enabled.

You're welcome, I'm glad you found it informative!

Yes, and you'll become more powerful after you master these tips 😄

Thanks for watching! 👍

I'm glad you found them helpful

Thanks for watching and commenting!

It's my pleasure!

Thanks!