CODEWITHSUNDEEP
excelvba
bbetnar
bbetnar

Reputation: 57

Insert VBA sub into Excel Worksheet

Good day,

I am using Excel 2013 and I would like to hide and unhide my Sheets as I work with them. I spent some time Googling around and found plenty of ancient posts on forums about adding VBA to modules, but that's not quite what I'm looking for.

On a main page where I spend most of my time using data, I have a button that shows a UserForm with a list of sheets in a ListBox. I choose the Sheet from the ListBox, hit OK, and it runs the following; 

Private Sub OKButton_Click()  
    ThisWorkbook.Sheets(JobListBox.value).Visible = xlSheetVisible  
    ThisWorkbook.Sheets(JobListBox.value).Activate  
    Unload Me  
End Sub

I would like it so when I have my new sheets created via VBA, I can populate the sheet with the following subroutine; 

Private Sub Worksheet_Deactivate()  
    Me.Visible = xlSheetVeryHidden  
End Sub

If anyone can let me know how I can make a Subroutine to insert this code into my sheets, I would greatly appreciate it.

PS: My fallback method is to, of course, just copy/paste the code manually... But I would prefer to automate it if possible.

Upvotes: 2

Views: 1202

Answers (1)

peege
peege

Reputation: 2477

Instead of adding the same code to each sheet, since they are all inside the workbook and you are really trying to execute a hide once any sheet in the workbook is deactivated, put this in the code for ThisWorkbook.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Sh.Visible = xlSheetVeryHidden
End Sub

You might be able to use more workbook events with your type of project.

Here is a list of the workbook events.

If you want to exclude your main page from this, you can modify this by adding an IF statement:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    IF Sh.Name <> "Main" Then
        Sh.Visible = xlSheetVeryHidden
    End If
End Sub

The main line of thinking being that if you have to put the same code into more than one object, let alone ALL of them, you are repeating yourself.

Check out the concept of DRY, or "Don't Repeat Yourself", unless you like it WET, "We Enjoy Typing". or "Write Everything Twice". Even if it's just going to be created programmatically, a chunk of code shouldn't have to exist in all your sheets exactly the same when you can have one piece of code have an incoming argument that is a worksheet.

This way, if you have to make a change to its behavior, you do it once. It's easily testable and less to keep track of or modify later.

So if you find yourself having to use the same code over and over, look to the parent object and try to find a way to pass the changing object or variable through as an argument to a singular piece of code, or module.

Also, this is probably why you aren't finding any results on inserting the same code into every sheet. It's not a good practice

Article on DRY

Upvotes: 4

Related Questions