Hide Toolbars & Restore Visibility
I have a procedure that stores the Commandbar names and visibility settings in an array on open. Another procedure hides all Commandbars on open and restores their original visibility settings on close. For some reason the "Standard" and " Formatting" toolbars don't return to visible on close.
My error log says "08/13/07 18:21:37 [QA.xls]MCommandBars.bRemoveMenus(), Error -2147467259: Method 'Visible' of object 'CommandBar' failed". I have tried manually testing the hide/unhide procedure with hard coded settings and checked the correct variables are being sent and all seems to be correct.
I am pretty confident that the procedure to store settings is correct but have included the code just in case, also just noticed the "Standard" and "Formatting" toolbars are the only two visible at programme start.
Public gsaMenus() As String
Public Function bMenuSettings() As Boolean
Const sSOURCE As String = "bMenuSettings()"
Dim bReturn As Boolean
Dim iCounter As Integer
Dim cbrMenu As office.CommandBar
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Hide Toolbars In Workbook
I ONLY want to show the status bar, worksheet tabs, horizontal and vertical scroll bars. I've tried using the code below but the column and row headings will not show on the first sheet i bring up, but it reappears on other sheets. everything else works fine ....
View Replies!
View Related
Show/Hide Toolbars For Current Workbook Only
I have a macro that starts when the workbook is open that hides many of the headers and toolbars. It is great and works fine unless you are working on another workbook and need to see those headers and tool bars. Is there a way to display and hide those items per workbook and not for the entire application. here is my failed attempt: Sub show_excel_headers() Dim wb As Workbook Set wb = ThisWorkbook wb.Application. ScreenUpdating = False 'Display Headings in all worksheets Dim wsSheet As Worksheet Dim sSheetStart Set sSheetStart = ActiveSheet wb.Application.EnableEvents = False For Each wsSheet In Worksheets wsSheet.Activate ActiveWindow.DisplayHeadings = True
View Replies!
View Related
Hide Toolbars When Workbook Protection Is Enabled
I would like to hide the standard and formatting toolbars in excel when i enable workbook protection, however I have no idea how to get excel to realise when workbook protection has been enabled, and how to hide the toolbars in VBA also. Also i would like to know how to do the reverse (how to show tooldbars when workbook protection has been disabled)
View Replies!
View Related
Restore Validation Using VBA
I want to do is allow a user to choose entries from a data validation list and then they can remove the validation to make some edits to the selection. I have it set up so that they can do this by clicking on a command button that will remove validation for the selected cells. What I want to now do is to be able to restore the validation in case they have made an error. My first thought was to have a hidden "Master" sheet that has all the relevant validation in it and then when the user picks a range to restore validation to, just copy the cells from the "Master" back to the working sheet (it should be noted that the range to restore validation to may contain cells with different validation). I'm sure the code for this is quite simple but I am still learning. The steps I want to do are: 1. Store the range of cells chosen on the worksheet 2. Go to the Master worksheet and select the same range 3. Copy the range from the Master worksheets back to the same range on the original worksheet.
View Replies!
View Related
Restore Cell Default Format
In a worksheet, I should have selected the region where data is present, and center align all the cells. Instead, I selected the entire worksheet and did the same, so that whenever new data comes in the relative cells are already formatted. This has caused the workbook size to increase dramatically. (So, I assume that changing a cell property from the default value uses some memory). Is there a way to restore the format of the blank cells to the default values, so that memory is freed and the workbook size decreases again?
View Replies!
View Related
Force Custom View On Restore Down Click
I need to create a macro that whenever the user clicks the restore down button it will not restore down, but either do nothing, or make the application display full screen. The following code runs when the workbook opens and whenever a different sheet is activated and I would like it to run when the user clicks the restore down button. Private Sub Workbook_Open() Application. ScreenUpdating = False Application.DisplayFormulaBar = False Application.ShowWindowsInTaskbar = False Sheets("01").Select With ActiveWindow .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False End With............................
View Replies!
View Related
Ensuring Visibility Of Text
I created an automation process where worksheets are generated. But after the sheets are generated I noticed that one of the company names is missing from the list. As I am watching the worksheets generate I can see the name quickly printing on the screen. When I use a message box to validate the data entry, the name does appear. After all is generated, the name is still missing. Does anyone know what this could be? Could it be a software malfunction?
View Replies!
View Related
Visibility Of Cell A1 On All Worksheets When Macro Finished
How can I loop through all worksheets in a workbook and, not only select cell A1 in each worksheet at the end of a macro, but have the visibility of cell A1, no matter where you last clicked on the worksheets, such as a remote cell like BK244. I would like the user to see the first row and further most left cell (cell A1) when they click any sheet in the workbook, because my macro causes several rows of data to be written, leaving the viewer to see something like row 244, instead of the top row which is where the titles are.
View Replies!
View Related
Set Control Visibility Based On Tag Property
I have a userform which uses the tag property of the frame to determine whether a frame is visible (and hence the controls that reside within the frame). A frame contains three combo boxes, and six text boxes. The tag property of the frame matches the number of frames that are visible on the form, so that if the user selects five frames, frames 1 through five become visible and for all other frames visible = false. A frame contains all the data for a single entry. There can be up to fifty frames/ entries that are visible on the form depending on the user selection. I want to use the visibility property to do two things: first the combo boxes are filled from an array after the user selects the number of frames (or entries). I only want to fill the comboboxes where the frame is visible. The second thing is that I have a function which uses the data from the text boxes and combo boxes as required arguments. Since these are required arguments, I will get a data mismatch error if I try to call it and the controls are empty. Therefore, I only want to call the function if the frame which houses the controls is visible. Public Sub Visible1(Entry As String) Dim ctrl As Control For Each ctrl In UserForm2.Controls If TypeName(ctrl) = "Frame" And ctrl.Tag <= Entry Then With ctrl .Visible = True End With Else If TypeName(ctrl) = "Frame" And ctrl.Tag > Entry Then With ctrl .Visible = False.............
View Replies!
View Related
Embed Picture Into Worksheet & Toggle Visibility
I am trying to embed a picture into a worksheet, hide the picture, then call it up later when a macro runs. I know how to insert a picure using a macro, but I want to embed the picture so that I can take the file with me that includes the picture and be able to call the picuture in my macro. Right now, the picture is on my hard drive and the picture will not load if I take the file to a different computer and run the macro.
View Replies!
View Related
Toggle Shape Visibility On Different Sheet Than Event Code
I have found that this only works on the sheet being viewed, how can I make it work on a different sheet than the one that I am on? Option Explicit Private Sub Worksheet_Calculate() If Range("C1").Value = "5" Then ActiveSheet.Shapes("rectangle 1").Visible = True Else ActiveSheet.Shapes("rectangle 1").Visible = False End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range)
View Replies!
View Related
Resetting Toolbars
my excel is no longer showing any tool bars and I can not get them back, is there any short cuts to bring back the menus? I can’t even do a right mouse click on the sells for some reason. The only tool bar that is showing is the formula bar.
View Replies!
View Related
Can I Automate Toolbars
I find that the picture toolbar opening automatically when I click on a picture useful (sometimes). Is there anyway of automating other toolbars to open (such as 'chart') when I click on an object? I've been looking at various add-ins in google - but can't find one to do that.
View Replies!
View Related
Remove The Toolbars
I have a slight problem with some code I am using. I have a user form where I force the users to enable Macros using the hidden sheets trick. Once the user enables the macros all of the toolbars dissapear from excel. The code I use to do this is below:
View Replies!
View Related
Saving My Toolbars
Everytime this meant that I had to start from scratch in order to have my customized toolbars. This is a bit inconvenient. Can I simply save some file (containing my XL toolbar settings) from some location and if I ever run into such a situation again, I can simply copy the saved file back into the same folder... Thanks in advance for your help and BTW would the solution work the same way for different Excel versions or?? My O/S is XP and I use both Excel 2003 (once in a while I also use Excel 97 but that is installed on my other old pc).
View Replies!
View Related
Toolbars And Add-ins
I created a custom toolbar in excel. by right clicking on the toolbar, creating new, adding a couple buttons and assigning them to a couple macros. I'd like to send this to people, how do i do this. Obviously, the toobars's would have to reference the macros, these i have in an add-in. Is there anyway that i could attach the toolbar's to the add-in so i could just send the add-in. I also created a menu, is there anyway that i can put this into the Add-in also? Where does the toolbar i created and the menu that i created save down. If i open a new excel without opening personal.xls, the toolbars will still be there (open one excel, then open another and it tells me that my personal.xls is in use by jason harris, i hit cancel so it opens without my personal workbook) Lastly, if I open excel, then go into VB, I can save my personal workbook. How do I save this as or save a copy?
View Replies!
View Related
Toolbars Empty Nubs
I use excel 2003 and have maybe 60-70 macros linked to buttons on maybe 6 custom toolbars. The macros are saved on a personal.xls that is saved in the XLStart folder so it will auto start every time I run excel. My excel crashed and my toolbars are all empty nubs and my personal.xls is still in the same folder but does not auto start...did a detect and repair no help...I manually started personal.xls no help...I found a thread that suggested looking for *.xlb didn't find it...a shared file with the same toolbars can be seen by other computers but not mine...
View Replies!
View Related
Toolbars Lost After Macros Used
A IT Ordering system has been created using Excel, I am prompted to enable Macros, no problem there. The problem is that when I exit the worksheet using a formulas setup as an exit button, my Excel is displayed with my Toolbars missing. I have been unable right-click at the top and re-add these I am only presented with the option to minimise, close etc. 1. How can I replace my Toolbars? 2. Is there a formula fix so that I can use this worksheet without losing my toolbars?
View Replies!
View Related
Prevent Customization Of Menus And Toolbars
I have a custom toolbar created in a workbook. I have coded my workbook so that the full screen is displayed, removing the title bars, status bars etc Application.DisplayFullScreen = True However, there is still another menu option I am not able to remove. It is the "Add or Remove Buttons" and when you click that the "Customize..." button also is enabled. I looked up some code on microsoft and only found code in office97 that gave me errors in 2003: Sub DisableCustomize() Application.CommandBars("Tools").Controls("&Customize...").Delete CommandBars("Toolbar List").Enabled = False End Sub The quote from the site says "To prevent others from changing the menus and toolbars in a custom application, remove or turn off the Customize option on the Tools menu and turn off the Toolbar List shortcut menu." I get an error saying invalid procedure when I run that. how to disable the button or prevent users from choosing it?
View Replies!
View Related
Custom Toolbars Visible In A Specific Workbook
I created 3 custom toolbars. I want toolbar A to be visible only in Workbook A.xls, toolbar B to be visible only in Workbook B.xls and toolbar C to be visible only in Workbook C.xls. while Workbooks A,B, and C are open at the same time. I use Workbook A to automatically open Workbooks B and C. Problem: Since I'm openning the Workbooks automatically it only keeps the Toolbar C visible on all Woksheets. I included the following code in the This Workbook Object on each Workbook: For Workbook A: Private Sub Workbook_Open() Application.CommandBars("A").Visible = True Application.CommandBars("B").Visible = False Application.CommandBars("C").Visible = False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.CommandBars("A").Visible = False Application.CommandBars("B").Visible = False Application.CommandBars("C").Visible = False..............................
View Replies!
View Related
Put Toolbars Back On Workbook Close
This code snippet works when executed from a worksheet (it puts Menu Bar back) but returns an error when used in the This Workbook module. Application.CommandBars("Worksheet Menu Bar").Enabled = True CommandBars("ToolBar List").Enabled = True CommandBars("Ply").Enabled = True
View Replies!
View Related
Find Customised Toolbars File Path
In Windows 98 for example when you created a Customised Toolbar and attached macros to it a file was created with the user name followed by 8. i.e. timbo8 You could copy this file and keep it was a backup in case you lost the toolbar/s. Which invariably happened whn someone else logged onto your pc. When the company transferred over to Windows 2000 this file was called something else I thing it ended in a .xla extension but I don't know what it was called or where it it located.
View Replies!
View Related
Remove Toolbars Upon Open/Re-instate Upon Close
Is it possible to use a function that removes all toolbars upon opening the workbook and reinstates the same upon closing, the work book i am currently using does the following as described in the code. however the function is limited in that only toolbars stated in the code will be removed, if for instance there was an adobe toolbar, that remains on the screen. or is there a better way of going about this? On open: Private Sub Workbook_Open() Run ("CreatePReturn") Run ("CreateProceed") With Application .CommandBars(" Formatting").Visible = False .CommandBars("Standard").Visible = False .CommandBars("Drawing").Visible = False .CommandBars("Return").Visible = False .CommandBars("Proceed").Visible = False .CommandBars("Worksheet Menu Bar").Enabled = False .CommandBars(" Chart Menu Bar").Enabled = False .DisplayFormulaBar = False .DisplayStatusBar = False End With With ActiveWindow .DisplayHeadings = False .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False End With.................................
View Replies!
View Related
Extra Toolbars Have Appeared At Top And Left Of My Worksheet
I find (what look like) two extra toolbars have appeared at the top (below the standard and formatting toolbars) and to the left of my worksheet. They have lines, dots and little boxes with an x or - in them, depending if you click on them or not. The +/- box function seems to be to make columns appear and disappear. How did I turn this function on and more importantly, how do I turn it off?
View Replies!
View Related
Hide Columns & Hide X-axis Labels
I am filtering the data displayed in a chart by hiding columns. I would also like to filter the X-Axis labels by hiding columns. If I do this manually I have no problems but when I run the following macro the chart gives a reference error for the X-axis labels. Sub ShowA2() Application. ScreenUpdating = False num = Sheets.Count Sheets("X-Axis").Activate Range(Columns(1), Columns(256)).Select Selection.EntireColumn.Hidden = False For a = 1 To 5 Sheets(num - a).Activate If ActiveSheet.Name = "A2 Data" Then Columns("A:Q").Select Range("A10").Activate Selection.EntireColumn.Hidden = False Sheets("X-Axis").Activate Columns("A:E").Select......................
View Replies!
View Related
Hide Value Other Than Zero
How can I hide the value in column H when there is no value in column E, but still be able to fill down the formula in column H as many rows as I want? In other words, I don't want to see the totals in column H in rows where the items haven't been sold yet (col. E) or in rows where there is no other data at all. Items are not always sold in the order in which they were purchased but I still need the formula in column H to be intact.
View Replies!
View Related
Hide #N/A
I have a big formula with 6 IF functions and i whant to display 0 when formula returns #N/A. I can't use IF(ISERROR()) because there are to many IF's functions
View Replies!
View Related
Hide On Open
Ok what i would like is when i open a certain excel file it hides the command bars the sheet prity much all of excel. though Must still be able to show the userform.
View Replies!
View Related
How To Hide #VALUE! In Cells?
There are empty cells in a column. Where the cells are empty, they are reading #VALUE! because I have copied down a formula in all other cells above and below etc. Is there any quick way of making the #VALUE! disappear or by hiding? (Other than the copied formulas in the column, there are also conditional formatting so when the numbers drop below zero value, it turns the numbers red). It's a long sheet and I want to avoid deleting each #VALUE! by hand!
View Replies!
View Related
Hide A Worksheet
I want to know how do I hide my "Raw Data" worksheets? I have about 10 worksheets in a workbook and just need to hide the first one - where all the raw data is store...
View Replies!
View Related
Hide Vs Unload
I use several different userForms at different times in my macro. I am having an issue where soemtimes Excel will be stuck (not frozen) after I close a form. Its almost like the form is being hidden but Excel thinks that it is still visible, so execution halts. I've tried leaving the form with both me.hide and unload me. My code is pretty lengthy, so I don't think submitting it is appropriate. I believe my problem is my own lack of knowledge on hide vs unload. Can anyone help me out here?
View Replies!
View Related
Hide Errors #Value
I am creating a spreadsheet which shows the date of when the training was cpmpleted. I have added an expiry column which gives the date 3 years from start date no problems. The trouble i am having is that the column will return an #VALUE when no training date is entered. ie: the person has had no training so no date can be entered.
View Replies!
View Related
Hide Worksheets ..
I have attached a spreadsheet, which has a master data sheet and 2 other worksheets (Sheet 1 and Sheet2) that have linked Choose Functions from the main data worksheet. I would like to send copies of Sheet 1 and Sheet 2 to seperate people, however would not like them to be able to view each others worksheets or the Master Data sheet, as it is sensitive data. Does anyone know how I could create a macro, that enables me to click a button which would protect & hide two of the worksheets including the name of the worksheet (e.g. protect & hide worksheet "Data" and "Sheet 2"). This way I could send the entire spreadsheet to someone while hiding the sensitive information.
View Replies!
View Related
Hide Various Columns
I have a very large spreadsheet that I work in and then print from. When I print, I need to hide about 20 columns which I do manually. I am curious if there is a way I can do this easier. If I could somehow filter columns, or set mutliple columns to a name for a dopr dowm box, or possibly a macro.
View Replies!
View Related
Hide The Formula
This is a simple division function using 2 cells. Normally if the cell referring to the numerator of the division forumla is blank you will get the "divide by zero error" (#DIV/0!). I have this sheet where there is no formula in the cell content (cell E1) but if I enter numbers in cell B1 and D1 it calculates: =(B1-D1)/B1 How is this formula hidden? Normally if I would want to hide #DIV/0! I would use an IF function but I would still see the formula and if fuction if I click on the cell. Whats odd the formula doesnt show when I click on the cell but once i enter data in the cells the forumla appears.
View Replies!
View Related
Hide All Rows With ZERO Value
Is there such a way/function that i could simple hide/exclude any row with ZERO value in either a pivot table or in a regalur table of data? As of now, i have to manually find the row with zero value and hide them individually.
View Replies!
View Related
|
TRACKER
