Autofilter and Ctrl+F find allows fairly good access.
I couldn't find the one answer so I came up with a 3 prong approach.ġ) I use Excel as a flat database with columns & dropdowns defining the broadest grouping say VBA, Formulas, Excel Application, Pivot Tables eg "VBA", then the next column broad grouping eg "Copying Ranges", then another eg "within the same workbook" and then the detail of the tip plus links if any plus a comment box for long entries.
I long struggled with your question on the storage of tips and code and accessing them. Set objHide = ActiveSheet.Shapes("shpHideSale") Set objToggle = ActiveSheet.Shapes("btnToggle") Was it a mathematical solution or more experimental?ĭim objHide As Shape 'Rectangle to hide contentĭim CellLink As Range 'Provide a cell link that can be used in formulasĬonst ON_COLOR As Long = 5287936 'Green RGB(0, 176, 80)Ĭonst OFF_COLOR As Long = 2171169 'Black RGB(33, 33, 33)
(Code Below) There are a few other improvements I have in mind to improve the abstraction and re-usability of the control, but this was just a quick and fun addition.Īlso, I have the same question as Gene: how did you determine the moveby value. Had an idea to add a fade effect to the example workbook for a smoother transition. A replacement for scrollbar control – picture calendar case study.If you like the on/off switch example, you are going to love these other examples. What about you? Do you like the on/off switch concept? How are you planning to use it? Go ahead and tell me in comments.
With a little bit of clever programming we can make one macro that can talk to all on/off switches and update their individual linked cells. You have to set up multiple macros if you want several of them in a workbook. It looks great and makes my workbook attractive. Examine the moveBtn2() macro to learn more. Play with it to understand this concept better. Download Example WorkbookĬlick here to download the example workbook. Note: This video shows another example of on/off switch (not the chart one you see above), but equally awesome.Īlternative ways to watch this video – on our YouTube Channel or Facebook Fan Page. To make you even more awesome, I made a short video tutorial explaining the whole thing. Video tutorial – On/off switch using Excel & VBA
How to make this on/off switch using Excel & VBA So I though, why not make an on/off switch like the ones we see in our iPhones / tablets. Although they are easy to use, check boxes are not very slick. So first take a look at it.Īs you know, there is a form control in Excel that behaves like on/off switch. I am not sure how to describe this new thing I made in Excel / VBA.