Free Microsoft Excel 2013 Quick Reference

VBA code to hide / show certain tabs

Hi
I have a spreadsheet with many tabs, and would like to have some control over which tabs to show.
I have a control sheet with a couple of list of tabs to show or hide (this lists will keep changing), is there a way for VBA to read that list and hide or, if already hidden, show those tabs only?
Ideally, I will link the macro to buttons and end users will be able to run with buttons for each separate list.

I have something simple to show only those with certain names, but it is not very user friendly.

Sub Show Sales Sheets()
Dim ws As Worksheet
On Error Resume Next
For Each ws In Sheets
   If ws.name <> "Control" Then
        If ws.name Like "Sales*" Then
            ws.Visible = xlSheetVisible
        Else: ws.Visible = xlSheetVeryHidden
        End If
   Else: ws.Visible = xlSheetVisible
   End If
Next ws
Sheets("Control").Activate
End Sub
Appreciate any help for a VBA newbie!
Thanks


I am looking for some VBA code to hide a subreport if there is no data in that subreport. Any help would be greatly appreciated. Thank you.

I was looking for some advice on VBA code to hide/unhide sheets in a dynamic named range. I have a monthly workbook (start a new workbook the first day of each month) and among the many sheets there exists one sheet for each potential day of the month, 1,2,3, . . . ,30,31. I already have dynamic named ranges with the names of the sheets that are weekdays and weekends respectively. For example, for June 2012, the weekdays dynamic range is a 22 X 1 array {1,4,5,6,7,8,11,12,13,14,15,18,19,20,21,22,25,26,27,28,29} and the weekends named range is a 8 X 1 array {2,3,9,10,16,17,23,24}. I would like to have code that hides/unhides both/either/or of these dynamic named ranges. I looked through previous postings and couldn't find something applicable. Any suggestions would be appreciated. Thanks.

Hi Guys,

Please, I need a vba code to hide Excel menus, toolbars, scrollbars. etc. ONLY FOR ONE WORKSHEET.

In my frantic search, I came across the following code:

	VB:
	
 Workbook_Activate() 
With ActiveWindow 
    .DisplayHorizontalScrollBar = False 
    .DisplayVerticalScrollBar = False 
End With 
With Application 
    .DisplayFormulaBar = False 
    .DisplayStatusBar = False 
End With 
With Application 
    .CommandBars("Worksheet Menu Bar").Enabled = False 
    .CommandBars("Standard").Visible = False 
    .CommandBars("Formatting").Visible = False 
    .CommandBars("Custom 1").Visible = True 'This is your custom toolbar
End With 
 
End Sub 
 
Private Sub Workbook_BeforeClose(Cancel As Boolean) 
    ‘HideToolbar 
    With ActiveWindow 
        .DisplayHorizontalScrollBar = True 
        .DisplayVerticalScrollBar = True 
    End With 
    With Application 
        .DisplayFormulaBar = True 
        .DisplayStatusBar = True 
    End With 
    With Application 
        .CommandBars("Worksheet Menu Bar").Enabled = True 
        .CommandBars("Standard").Visible = True 
        .CommandBars("Formatting").Visible = True 
        .CommandBars("Custom 1").Visible = False 
    End With 
End Sub 
 
Private Sub Workbook_Deactivate() 
     'HideToolbar
    With ActiveWindow 
        .DisplayHorizontalScrollBar = True 
        .DisplayVerticalScrollBar = True 
    End With 
    With Application 
        .DisplayFormulaBar = True 
        .DisplayStatusBar = True 
    End With 
    With Application 
        .CommandBars("Worksheet Menu Bar").Enabled = True 
        .CommandBars("Standard").Visible = True 
        .CommandBars("Formatting").Visible = True 
        .CommandBars("Custom 1").Visible = False 
    End With 
End Sub 
 
Private Sub Workbook_Open() 
    With Application 
        .CommandBars("Worksheet Menu Bar").Enabled = False 
        .CommandBars("Standard").Visible = False 
        .CommandBars("Formatting").Visible = False 
        .CommandBars("Custom 1").Visible = True 
    End With 
    With ActiveWindow 
        .DisplayHorizontalScrollBar = False 
        .DisplayVerticalScrollBar = False 
    End With 
    With Application 
        .DisplayFormulaBar = False 
        .DisplayStatusBar = False 
    End With 
     
End Sub 
[/B] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
[/FONT][/COLOR]

When I tried the code, I realised that it applies to an entire workbook and therefore does not meet my needs. I should be grateful if the code could be adjusted so that I can use it for just one worksheet – the ‘Main Menu’ sheet in a workbook.

I have three worksheets in a workbook. The first worksheet is the 'Main Menu' sheet, and the others are 'Database' and 'Analysis', respectively. The ‘Main Menu’ sheet houses company’s name and logo, including the command buttons that enable me to get to the other sheets with a click of a button.

Please I need help to alter the code so it will work for only the ‘Main Menu’ Sheet without affecting the other worksheets in the workbook. If not possible to alter this to meet that need, I’ll appreciate an alternative code that will do the job.

Thanks in anticipation of your kind help.

Buddy

Hey,

Thanks in advance for any help. I have a workbook in which I have multiple worksheets which are dependant on one input page. I have vba code to hide and unhide blank rows in one worksheet. What I would like to do is be able to hide corresponding rows in another worksheet. I have run into 2 problems. First, I can write code to check for blank rows, but I want it to update on every enter keystroke and this cycles through 2000 lines and takes too long. I would like to have it execute only when a cell changes. Next, the rows contain the same data, but are arranged in a different order so that row 4 on one worksheet is not the same as row 4 on the other worksheet. Any suggestions?

thanks,

Mike

Hi.

I need to create VBA code to hide empty columns. Problem is when column considered empty it always has header row(s). I guess code should have defined Range of rows and columns such as A2:AZ50. In this case row is header. Thanks for your help.

Is there any VBA Code to hide the dark box that indicates the currently
selected cell? My sheet I have has no need for this to be seen. Hiding it
would be nice. TIA!

Scott

Hi,

The only way i know to hide / show the sheet tabs is through the Office button, Options, Scroll Down, tick the box.

Is there a shortcut key or a way to code this into a button to go on the ribbon?

Cheers

p.s. I'm not talking about hiding or unhiding a sheet, it is the visibility of the sheet tabs i want to remove.

Updated request 3/17/11

I need some code to hide or delete all the rows without specific text.

Here is the scenario. . .
I have an excel sheet with 6000+ rows of data. Column B has numbers (which represent the accounts). There are about 100 account numbers that repeat because each line represents one month of data. I need to show 13 specific accounts, but they need to show each month for each account. (The macro needs to hide all rows except the rows with the 13 accounts.) For the sake of ease we will say that the account numbers bagin at 1 and end at 100. I need to show the following account numbers. . . 5-12, 27, 59,60,72,99.

I have this macro, but it only works on one number. Can anyone change either of the codes below to allow the 13 sets of data to remain and delete or hide all the rest?


	VB:
	
 NotDelete() 
     'this macro deletes rows without specific text but only works on one condition
    Dim rng As Range, cell As Range, del As Range 
    Set rng = Intersect(Range("B:B"), ActiveSheet.UsedRange) 
    For Each cell In rng 
        If (cell.Value)  "53060340" Then 
            If del Is Nothing Then 
                Set del = cell 
            Else: Set del = Union(del, cell) 
            End If 
        End If 
    Next cell 
    On Error Resume Next 
    del.EntireRow.Delete 
End Sub 
 
 
Sub HideRows() 
     ' this hides rows on your sheet, but I would like this to hide all rows except what is designated.  I like this macro
because you can  enter new IF lines to add multiple conditions
    For Each cell In ActiveSheet.Range("A4:A6000") 
        If cell.Value = "enter your condition here" Then cell.EntireRow.Hidden = True 
        If cell.Value = "enter your condition here" Then cell.EntireRow.Hidden = True 
        If cell.Value = "enter your condition here" Then cell.EntireRow.Hidden = True 
        If cell.Value = "enter your condition here" Then cell.EntireRow.Hidden = True 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for you help

On several worksheets column h has a numerical value. If the numerical value is 0 I would like the row to be automatically hidden. Can you suggest the code to place in the VBA module on each of the worksheets I want this functionality?

Hi All,

I need help with vba codes that are not working properly. I have three worksheets in a workbook namely:

Menu
Balance Sheet
Income & Expenditure

On the ‘Menu’ sheet I created a textbox button each for the other two sheets including one button for ‘Exit’:

My aim is this: When the ‘Main Menu’ sheet is showing and I click on the button for e.g. ‘Balance Sheet’, it would take me to the ‘Balance Sheet’ sheet and hide the other sheets (i.e. the ‘Menu’ and the ‘Income & Expenditure’ sheets.

On the ‘Balance Sheet’ sheet, I have a button for ‘Menu’, and when I click the button, it should take me back to the ‘Menu’ sheet and hide the other sheet including the ‘Balance Sheet’ Sheet itself.

On the Menu sheet, I created textbox buttons and assigned macros to take me to the those other sheets. And on each of those other sheets I created a ‘Menu’ button to take me back to the Menu. For e.g. on the ‘Balance Sheet’ sheet, I placed the following codes:


	VB:
	
 TextBox1_Click() 
    With Sheets("Balance Sheet") 
        .Visible = True 
        .Activate 
    End With 
    Me.Visible = True 
End Sub 
 
 
Sub Button27_Click() 
    With Sheets("Menu") 
        .Visible = True 
        .Activate 
    End With 
     
    Me.Visible = False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

The first code takes me from the Menu to the Balance Sheet – hiding the Income & Expenditure Sheet but it DOES NOT HIDE the Menu sheet itself. However, the second code takes me from the ‘Balance Sheet’ back to the Menu. In this case it succeeds in hiding itself (the Balance Sheet ) and also the Income & Expenditure Sheet.

I also placed two such codes on the ‘Income & Expenditure’ Sheet and I get the same result.

My problem here is that I am unable to figure out the appropriate code to place in the Menu sheet or to attach to any of those other codes to achieve the ‘hiding’ of the Menu sheet. For e.g. When I click on the button for ‘Balance Sheet’ it should take me to the Balance Sheet while hiding both the Menu sheet and the Income & Expenditure sheet. The second part of it is OK i.e. when I am on the Balance Sheet and click on the Menu button, it takes me back to the Menu and successfully hides the other sheets.

Can someone help me please.

PS: It is possible I might have messed things up a bit by creating the Menu buttons using textbox buttons and the sheet buttons using command buttons. It is too late for me to scrap them and start all over. They work partially. All that I want is a tweaking of the codes so that when I go from the Menu, that particular sheet should be hidden along with the other sheet that I am not going to.

Thanks.

Buddy

I am looking for vba code to move data from one spreadsheet tab to another. I need it to copy based on greater than or equal to and less than or equal to a certain column. I saw other related threads with just specific values; I need a range of numbers.

Attached is an example of the before with the data download, and the after when I just manually move them over to each tab. The tabs noted GT100K or GT50K means in column I values over 100,000 or 50,000, etc.

So the code would pull based on column C or C & I from the data tab, and copy to the other tabs. After moved If you could sort the tabs by B&C too, that would be perfect. If this just pulls from the top row down, then you can ignore the prior sentence since it will be sorted before running the code.

I'm not sure if my files are working so here is kind of an brief example. If you can give me the base, maybe I could manipulate the vba for my specifics.

Example :
Account Var. Amount
45000 $105
45005 $50
56100 $25
79008 $125
67002 $25

The code would move the data (first 2 rows) in one tab for accounts 45000-56009. Then move to another tab for accounts 45000-56009 and variance amounts greater than $100 (just the first row). Move data from accounts 56000-56999 to a separate tab (copy the third row into new tab). Etc.

Let me know if you have any questions.
Thanks,
Preston

Is it possible to hide the Main Toolbar (the one that contains "START") using VBA in Excel? If so, could someone be so kind as to give me a sample of the code.

Thanks

Ted...........

Hello folks,
I'm new to the site and have been having a problem trying to create some VBA code for an Excel spreadsheet.
I am trying to create an input sheet with a specified number of rows. My plan was to hide all the empty rows in the table and ask how many rows were required in the table. A button would then be pressed to unhide all the relevant rows thus giving a table of the correct size.
My best effort so far looks something like this:

	VB:
	
 UnhideRows() 
    Dim i As Integer 
    Dim myRow As Integer 
    myRow = Range("A1") + 2 
     
    Application.ScreenUpdating = False 
    For i = Selection.Rows.Count To 1 Step -1 
        If WorksheetFunction.Sum(Selection.Rows(i)) > myRow Then 
            Selection.Rows(i).EntireRow.Hidden = True 
        End If 
    Next i 
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
the cell to give the size of the table is A1 and the table starts in A3. The first cell in each row has an index number (=row() - 2), all other cells would be empty to begin with.
My attempt did not work.

Can someone please give me some help on this, it would be much appreciated.

Thanks.

Hi... I need some vba code that will hide (xlVeryHidden) all active sheets but the active sheet. Here's what I tried:

Sub HideAllSheets()
Dim Wks As Worksheet
For Each Wks In Worksheets
If Wks ActiveSheet Then Wks.Visible = xlVeryHidden
Next Wks
End Sub

Obviously, it's not working so if you can lend any advice, I would be most grateful!

I have been looking for some VBA code to display the Task Manager Applications. I have found many examples that show the information under the Processes tab, but this does not help me in my quest to find the names of currently open notepad documents.

I found one example that listed the applications, it worked in Word, but not in Excel, even when I enabled the reference library. Can anyone help?

Many thanks in advance.

Hello

I have referred to a previous discussion called "VBA Code to delete rows which contain a certain character" but whilst this has helped, I could do with a bit more guidance if possible.

A sample of the data I'm working with looks like this (in column C):

Microsoft Office XP Standard
Security Update for Windows XP (KB927802)
Windows XP Hotfix - KB918439
Windows Installer 3.1 (KB893803)
Shockwave
Squirrel SQL v2.1
Update for Windows XP (KB911280)
Screensaver
etc...

I would like to find out the best way of deleting all the rows that contain the following text:
"hotfix"
"security update"
"windows installer"
"update for windows XP"

I was thinking along the lines of the suggestion offered in the "VBA Code to delete rows which contain a certain character" - to use autofilter but even using macros, I am struggling to turn this into code.

Any help would be much appreciated.
Thanks
Grace

hi first thread so please help

is it possible to enter vba code to hide and unhide worksheets under certain conditions, i am a personal trainer and am writing an exercise programme however now have about 15 worksheets and would like to make it more user friendly is it possible for example after if a cell that contains the number of workouts is greater than 20 the currant programme on worksheet 1 to hide and worksheet 2 to appear then after 30 workouts worksheet 2 to hide and worksheet 3 to appear etc etc

thanks in advance all

carl

Hi,

I have an excel file containing 50sheets; want to check if any cell in any sheet/tab has any #REF error; if error exists it has to show me the "Sheet name" & "Cell" (may be in a pop up window or something)

Can anyone please help me with VBA code to get the above job done?

Thank you.

I have 9 charts, and to select & show one at a time, I used an image and assigned a formula to it, using a named range " getChart" and a Combo Box. This method works OK except that it is causing Excel to be so annoyingly sluggish.
To overcome this sluggishness, I need a VBA code which sets the formula for the image ( forcing the chart to change) and deletes the formula from the image again to leave it ' static'.
I've noticed that when I manually delete the formula, the selected chart stays and Excel sluggishness goes away completely, but without the formula no other chart can be selected. A sample workbook is attached
Any help will be greatly appreciated.

Is there VBA code to render a userform unusable after a certain date?

I have set up my spreadsheet and wriiten vba coding to save to the data sheet with the following details (see below) and this work perfect.....however what i also require is for this to be saved as well into indiviual tabs that have been set up (tab name is the VRN for each vehicle) approx 100+ tabs. Is there a simple addition to the vba code that will allow the user form input to write to the main data sheet and the relevant vehicle sheet?

eRow = wsData.Range("A" &
Rows.Count).End(xlUp).Offset(1, 0).Row

With wsData
.Cells(eRow, 1) = cboWkNo.Text
.Cells(eRow, 2) = TxtDateFill.Text
.Cells(eRow, 3) = cboDepot.Text
.Cells(eRow, 4) = txtDriverName.Text
.Cells(eRow, 5) = txtVRN.Text
.Cells(eRow, 6) = txtMakeModel.Text
.Cells(eRow, 7) = txtFuel.Text
.Cells(eRow, 8) = CheckBox1.Value
.Cells(eRow, 9) = CheckBox2.Value
.Cells(eRow, 10) = txtOdo.Text


Hi All,

I have a dynamically updated spreadsheet (each new row is appended below the last active row in close to real-time - there is a loop that updates code every second), thus very soon the screen is filled and while new data (new rows) are updating, I can not scroll down (basically the macro is working but the screen kind of freezes). What I want is to see only the last 15 rows and hide the rest of the rows (with data) so I have the snapshot of the first and the latest data as it is being updated.

My attempt to do this is below (the first chunk of code is regulating the updates of data i.e. new rows), Test() is working fine so I did not include it here. HideMe() is meant to hide all rows except the last 15 rows. I am only working in a worksheet 'Ratio' the entire time.


	VB:
	
 
Sub Calculator() 
    Dim i As Integer 
    For i = 1 To 4500 
        Application.Wait Now + TimeValue("00:00:01") 
        Call Test 
        Call HideMe 
    Next i 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

	VB:
	
 HideMe() 
    Dim ratio As Worksheet 
    Dim last, i As Integer 
    Set ratio = Sheets("Ratio") 
     'Disable Screen Updating
    Application.ScreenUpdating = False 
     'Determine last Row that contains data:
    last = ratio.Range("C" & Rows.Count).End(xlUp).row + 1 'this is the last row, (updates a new row below the last non-zero
row) - this part is working 'fine.
     'Hide all rows
    Rows("2:" & last).Hidden = True 
     'Loop through rows
    For i = (last - 15) To lastRw 
         'Hide preceding rows (leave only last 15 rows)
        Rows(i).Hidden = False 
    Next i 
     'Enable Screen Updating
    Application.ScreenUpdating = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks much,

Andtherus

I am working on a VBA application for Excel that should work in Windows versions 2000 thru 2010, and Mac 2004.

In my dBase3+ and FoxBase+ days, I would create and store a line of code to a variable based on If...Else...EndIf conditions and then execute the code via the variable. For example:


	VB:
	
 County = "KERN" 
mIndex = "Index On Well_Number For County = "KERN"" 
Else 
    mIndex="" 'for do nothing
    EndIf 
     
     'the first character "&" means execute code stored in mIndex
    &mIndex 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can this be done in VBA to get around compile errors?

First example is for showing a Userform Modeless-Model which Mac Excel hates (ie, compile error)


	VB:
	
 
    mUserform = "Userform1.Show" 
Else 
    mUserform = "Userform1.Show vbModeless" 
End If 
 
&mUserform 'variable to execute the code in mUserform

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The second example is to deactivate the autosave and-or autobackup setting. Mac Excel hates ThisWorkbook.EnableAutoRecovery (compile error: method or data member not found)


	VB:
	
 
    mSaveSet = "Application.SaveInterval = 0" 
Else 
    If application.version = "9.0" Then 
        mSaveSet = "Application.AddIns("Autosave Add-in").Installed = False" 
    Else 
        mSaveSet = "ThisWorkbook.EnableAutoRecover = False" 
    End If 
End If 
 
&mSaveSet 'variable to execute the code in mSaveSet

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Again, I want to save VBA code to a variable based on condition(s) for later execution, and to avoid compiler errors

Hi,

I was wondering whether someone may be able to help me with my problem.

I have data in Sheet 1 that gets entered from a form. It has a range of issues and times in hours that it took to deal with those issues. Also entered from the form is the date the issues occurred and from this I have columns that have the week numbers and month names in.

What I am struggling with is to come up with the VBA code to move the data (the issues and time it took to sort them) into Sheet 2 and arrange them in 2 columns. I would like to do this either with a button or the change event of two combo boxes with the week number and month names in them. Ideally if there are duplicates of the issues then I'd like to sum the hours taken to sort the issues and show in a third column how many occurrences there were of that particular issue and show just the one issue name but even if I could get just the issues and times into 2 columns on Sheet 2 I would be happy, I could sort it manually from there if necessary.

At the moment on the UserForm I have 12 comboboxes that have regular/common issues and 12 textboxes for irregular/one-off issues. On Sheet 1 I currently have columns that count the number of regular issues with CountIf formulas (12 columns) and then on a separate Summary sheet I have SumIf formulas that sum these regular issues for each week and month. The calculations do noticeably slow the spreadsheet down but if I could get the VBA code to work I could do away with the calculations and just run the code when I need to produce the reports which would be so much better.

I have been trawling through different threads on here for quite a few days now trying to find some code that I might be able to adapt but I think my knowledge of VBA is not yet good enough. I have learnt quite a bit though I think, which is good, but I have to admit defeat in getting this right. I have attached an example file to show what I'm trying to achieve.