Hide & Show CommandBars/Ribbons In 2007
Jul 1, 2007
I am not sure I like Excel 2007 yet, I have been playing around with it all day and while it has some good features, it is hard to fathom in places. I expect it is just a matter of time to get used to it.
With previous versions of Excel, hiding and un-hiding commandbard and toolbars can be achieved in a number of ways, such as with the code below
ActiveWindow.DisplayHeadings = False
Application.DisplayFormulaBar = False
Dim cbar As CommandBar
For Each cbar In CommandBars
If cbar.Enabled And cbar.Type = msoBarTypeNormal Then
cbar.Visible = False
End If
Next cbar
CommandBars("Worksheet Menu Bar").Enabled = False
This does not work in Excel 2007, I have tried many methods but all seem to be redundant in 2007 -
View 6 Replies
ADVERTISEMENT
Feb 28, 2013
Is there a way I can have my file, when opened, have the ribbons along the top of the screen automatically hidden from view, and unaccessible by the user, no matter who opens the file.
If there is a way, is there also a way that a single user (the creator) be able to over-ride this, and therefore have access to the ribbons for editing purposes.
View 5 Replies
View Related
Aug 5, 2008
In XL 2003, the w/b displays correctly the custom CommandBar on the w/s and it works fine.
The name of the CommandBar appears (and is checked) in the drop-down list if I right-click on the toolbar (list showing Standard, Formatting, Borders, Chart, ...., Customize).
In XL 2007, the w/b opens fine, but I couldn't get the custom CommandBar to display !!
I used F8 to step into the macro that creates the CommandBar. No errors, but still no display!
View 9 Replies
View Related
Sep 26, 2008
I want to 'repurpose' some of the menus on the Excel 2007 ribbon, in particular to set paste to paste only values. Effectively I need the equivalent of the following for Excel 2007
Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
Application. CommandBars("Edit").Controls.Item("Paste").OnAction = "New_paste"
Application.CommandBars(" Cell").Controls.Item("Paste").OnAction = "New_paste"
End Sub
.
John Walkenbach suggests the following xml code
<customUI xmlns = "http://schemas.microsoft.com/office/2006/01/customui">
<commands>
<command idMso="Copy" onAction="New_paste"/>
<commands>
</customui>
View 5 Replies
View Related
Aug 5, 2008
I need a macro that switches the Ribbon to the "Home" tab.
View 2 Replies
View Related
Apr 28, 2007
Having trouble making the TooltipText operate with the Mouse/curser passes over the Commanbars control
Set NewItem2 = Application. CommandBars(1) _
.Controls("Save Data to csv File").Controls.Add
With NewItem2
.TooltipText = "Save csv file to the Same Folder as Generator"
.Caption = MenuItemName19
.OnAction = MenuItemMacro19
.FaceId = 239
.BeginGroup = True
End With
View 7 Replies
View Related
Nov 13, 2009
I create and load a commandbar, prior to loading it I attempt to check if it exists, then delete it, and recreate.
I dont understand why this seemed to work for months and now creates an error.
It appears that every now and then the created commandbar is not created when I open a file. Thats when the error pops up. I can manually run the Create_Bar sub and it will be fine, for a while.
Run-time error '5':
Invalid procedure call or argument
This section is in my personal.xls file in "ThisWorkBook" of personal.xls.
View 5 Replies
View Related
Apr 14, 2014
I'm trying to create a sumifs that has 'before date X' and 'on or after date Y' as two of the criteria.
In the past, I have simple done a DATE(x,y,z) function inside the sumifs, but I'm trying to change this for reasons that would take a little while to get into. Let's just say it would make my coworkers lives much easier.
My hope is to get these date range criteria by referring to ribbons which have the dates in mind in them. Here is the formula I have now, and an example. See the red part of the formula.
Say I'm trying to have before Aug 1, 14 and on/after July 1, 14 as two criteria. FBP column A is where I would have the dates that this criteria would search through:
B1: 7/1/14
C1: 8/1/14
Formula: =SUMIFS(FBP!$G:$G,FBP!$B:$B,"=E",FBP!$A:$A,">="&B1,FBP!$A:$A,"
View 5 Replies
View Related
Mar 30, 2014
get my code to work.
I have a sheet with a dropdown box in cell "J1" which is meant as a 'Show only rows containing this value'.
Column Q, from row 3 downward has a pick box which lists the same values as the "J1" drop down box, but uses code to combine whats chosen with commas. An example of what is in a cell in column Q is "SeaHawk, BlackHawk, Squirrel, MRH", where cell J1 would be either "SeaHawk" or "MRH" or something completely different.
I need to only show rows, with text in column Q containing the smae text as J1.
View 8 Replies
View Related
Mar 17, 2009
My Excel>Preferences>View>Comments is set to Comment Indicator Only.
Working from keyboard and mouse, when I click on a cell holding a comment ($B$52) , the comment appears. When I click on a different cell the comment disappears.
When I run the code
...
Range("B52").Select
End Sub
the cell is selected, but no comment appears.
When I run
...
With Range("B52")
.Select
.Comment.Visible = True
End With
End Sub
the comment remains visible even after I click off of B52.
I get the same behaviour when I use Application.Goto rather than Select.
I would like to write a routine that
1) creates a comment for cell B52
2) selects B52
3) shows the comment in B52
< VB routine ends >
4) comment disappears when user clicks on different cell.
Does this require event code?
View 9 Replies
View Related
Mar 10, 2006
I have two options in a Cell as Applicable and Not Applicable. This is in a Drop-down menu and what I want is that when I choose Applicable, it should bring up additional 5 or 6 cells for me to complete. And if I choose Not Applicable, the additional cells should not show up. The default would be Not Applicable.
View 9 Replies
View Related
Dec 29, 2006
I bet you're getting sick of the "X" questions, and I have done an exhaustive search but the search filter kills searching for (X) or 'X' or "X" .. but nothing I did find is what I am looking to do. I have any # of userforms that when you click the "X" in the top right side, it will hide the userform... once.. Is there a fix to the code below to make it do it every time its clicked?
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = 1
UserForm15.Hide
UserForm11.Show
If CloseMode = 1 Then Cancel = 1
UserForm15.Hide
UserForm11.Show
End Sub
Like I said, I don't want to disable it, I tried that off of one of the posts I found and thats just plain annoying, I just want it to hide the one userform and show the other. This will do it once and then you can click it all you want and it wont do anything.. see the annoying trait above.. this lasts until you exit the program.. so the X button can be hit repeatedly though out the day. The userform 15 is visible, userform 11 isn't shown, IE not behind userform 15.
View 5 Replies
View Related
Jun 1, 2007
how to creat a userform with a button (1) to show anouther userform with a button (2). you click the button on 1 and it shows 2. then when you click the button on 2 and it shows 1. I'm having a problem with the second part.
View 5 Replies
View Related
Jul 24, 2007
Is there a method of formatting specific cells to show zeros? Or indeed I could turn on show zeros in options and then hide the ones I don't want. Any tips / advice on how to selectively display (or hide) zeros appreciated.
View 3 Replies
View Related
Jan 10, 2014
I have a some data in 1st column in which most of the data has decimals. For ex. 209.8, 224.4 like this. I have converted it by roundup function and get the exact result. I want to hide the decimals means not show decimals. Only the result will show like. 210, 224 etc. For your reference I have attached the sheet. I have manually typed this in the C column result area. That I need in A column.
View 9 Replies
View Related
Jan 5, 2007
Is it possible to hide the contents of a column that would only show once clicked on?
That way i could have a column called "Keywords - click to open" & the contents would only show once clicked on?
I have uploaded an example excel spreadsheet : example show hide.xls
View 9 Replies
View Related
Apr 2, 2009
I have a workbook that I want to show or hide a selection of cells depending on another cell when printing it.
Example
If cell x = A2A then display a certain cell set if anything else then exclude cell set from printing.
View 2 Replies
View Related
Jul 27, 2013
I've seen excel sheet view like this [URL].....
View 2 Replies
View Related
Mar 8, 2014
I'm trying to only show specific sheets per user using the environ variable and this code seems to work for the single user / sheet but the master user does not function correctly i.e. the code does not show all sheets, this is the code I am using:
[Code] ......
Why the above code does not respect the Master User "Jane" should be able to see all sheets?
Original source for this code was found here:
HTML Code: [URL]....
View 7 Replies
View Related
Feb 24, 2009
I'm creating an excel workbook that will allow any business or accounting student to go in and create their four year plan while giving them all of their requirements for their particular degree.
One of the features I wanted to have with this program is that it wouldn't display all the worksheets for each major, but rather the ones you choose that you are associated with. I was going to have a drop down menu on the first page that allows you to choose your majors, those being accounting, or business administration with its 5 focuses.
If I just allow all the worksheets to be shown, there would be 6 in total and I thought it might get confusing for a student who's only taking one of the 6. My hope is that on the first page, when you choose one of the options, it would automatically display the hidden worksheet that goes along with that major.
View 6 Replies
View Related
Jul 1, 2013
Basically, I am looking for a macro code to link to an 'Update' button that will do the following;
I will have 2 sheets, one sheet will be referencing data from other sheet with the following
=IF(Log Sheet!A4="Yes",(A1),"0")
This means, if it needs to be 'referred' it will go to the other sheet, and if it doesn't, then it wont and it will show up as 0.
Basically, every row will have this type of formula for 30 columns, and i would like a macro code that will hide values of 0, but even once hidden, will then show them again if in the other spreadsheet i change the value to yes, meaning i want the previously hidden row to show.
View 1 Replies
View Related
Jul 2, 2009
I'm trying to create a macro that will hide all the rows where the value in column E is equal to zero.
I'm currently using rows 1:700, but I may add to it.
View 9 Replies
View Related
Nov 19, 2009
I have looked through alot of this board and see the codes to hide all tabs old and new and code for each sheet.
My question is:
Is there a macro that can be used to do this as well. Say a button to hide all tabs and a button to show all tabs or does it only hide/show when the workbook is re-opened?
View 9 Replies
View Related
Feb 17, 2010
I have a spreadsheet that has been set up with totals at row 1010. The data that the spreadsheet contains only goes up to row 159. However data will continue to be added row by row over time. At the moment I have to keep hiding and unhiding rows to check the totals. Is there a way so that the spreadsheet will automatically hide all but five rows between the last row with any data in and the totals at the bottom of the spreadsheet?
View 9 Replies
View Related
Sep 18, 2008
I've been trying to make use of 'Run "doit", but the macro stops when it gets to my sheets called 'RST' and 'RST Pivot'. What I'm I doing wrong?
Sub DoIt()
Application. ScreenUpdating = True
With Sheet1.Shapes("Rectangle1")
.Visible = msoTrue = (Not Sheet1.Shapes("Rectangle1").Visible)
End With
'Toggling sheets Forces Rectangle 1to show while code is running
Sheets("RST").Select
Sheets("RST Pivot").Select
End Sub
Also, do I need to change 'With Sheet1.Shapes' to reflect the actual sheet name?
View 3 Replies
View Related
Jun 26, 2006
I have a worksheet with 4 sections of rows. Each section I call Goal 1 - Goal 4. I want to show each section and hide the other sections by clicking on a button. I have used a column, P, to denote which Goal a row belongs to by entering 1, 2, 3 or 4 accordingly. This is the code I am using.
Private Sub ToggleButton1_Click()
With ToggleButton1
.Caption = "Goal 1"
End With
Dim rCell As Range
If ToggleButton1.Value = True Then
For Each rCell In Range("P2:P99")
rCell.EntireRow.Hidden = rCell > 1
Next rCell
3) Else
Range("P2:P99").EntireRow.Hidden = False
End If
End Sub
I have three questions -
1) This code works to show Goal 1 and by changing the >1 value to <4 I can make it work for Goal 4 - but I can't work out how to show the other Goals, 2 and 3.
2) Is this code an efficient way of doing what I want?
3) How can I make the "up/down" state of the toggle button actually relate to whether or not I am showing a particular goal? That is, if I click Goal 1 and then click Goal 4, I am showing Goal 4 but both buttons stay in the "down" state. I want the Goal 1 button to automatically return to it's "up" state when I click on another button.
View 2 Replies
View Related
Aug 8, 2006
code to write down to make a checkbox hide the text in a few cells..
What I want is..
When I tick the check box I want it to show me the text in the cells (C11:D11 to be specific) and then when I untick it I want it to hide the text so all you can see is the background colour.
Here is the attatchment of what I have so far:
View 4 Replies
View Related
Sep 18, 2006
I have a commandbutton that when clicked, it would make monthview1 visible. I can do this via the following
Private Sub CommandButton3_Click()
MonthView1.Visible = True
MonthView1.Value = Date
End Sub
However, I do not know what code to add so that when the same commandbutton is clicked, the monthview object would hide.
View 2 Replies
View Related
Dec 13, 2006
I need some code that will do the following in a UserForm:
If option1 is selected = textbox1 is visible, textbox2/combo1 is hidden
If option2 is selected = textbox2 is visible, textbox1/combo1 is hidden
If option3 is selected = combobox1 is visible, textbox1/textbox2 is hidden
View 5 Replies
View Related
Dec 28, 2006
I would like to be able to use the before save event to hide some sheets before the save then after unhide some sheets. So that the user carrys on with the sheets they had before saving but when the document is reopened the correct sheets are hidden.
This is what I have so far but unfortunately when you click close and then save changes it runs the before save code and then goes around in circles, reasking the user if they want to save changes
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim ws As Worksheet
Cancel = True
Application.EnableEvents = False
Sheets("Protected Content").Visible = True
For Each ws In Worksheets
If ws. Name <> "Protected Content" Then ws.Visible = False
Next ws
Me.Save
For Each ws In Worksheets
ws.Visible = True
Next ws
Sheets("Protected Content").Visible = False
Application.EnableEvents = True
End Sub
View 3 Replies
View Related
