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 14 RepliesI want a code that removes all toolbars from excel when the .xls file is opened, then restores all toolbars back to their original setting when the .xls file is closed.
View 9 Replies View Relatedis there any way that i can remove the excel title bar and scrolling tabs so that the excel sheet do not resemble an excel document?
View 2 Replies View RelatedIs 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.................................
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?
I lost my command bar and tool bars (excel 97) all I have is the blue bar on top then the column letters. How do I restore them?
View 9 Replies View Relatedmy 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 10 Replies View RelatedI 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.
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).
I am looking for a macro to hide all toolbars i am using this at the moment
View 4 Replies View RelatedA 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 3 Replies View RelatedI 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 6 Replies View RelatedI 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 ....
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
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
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?
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?
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..............................
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
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 3 Replies View RelatedIn 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.
I have created a spreadsheet and wanted to originally see if I could save as a separate format (that makes it more like an application than an Excel spreadsheet). Having investigated, I found that full screen mode pretty much does what I want, however you can restore the menus by pressing ESC or selecting restore down. Is there a way of preventing users from exiting full screen mode, I could add a close application button for them to use instead.
View 3 Replies View RelatedI was working on an Excel 2003 workbook with 3 worksheets. The first sheet is the 'Menu' sheet on which I placed buttons to take me to the other two sheets and back. I wanted to hide the Excel menus, toolbars, scrollbars, etc. on the 'Menu' sheet so that no user can alter that 'Menu' sheet. The 'hiding' code was not meant to affect the other two sheets.
The code worked fine. However, when I closed the workbook and tried to open a NEW workbook so that I could do some other work, I realised that the new workbook and its accompanying sheets had the menus, toolbars, scroll bars, etc. hidden. I closed the workbook and tried opening a new workbook. It was the same experience.
I called up the file with the code, deleted all the codes attached to that file, and re-saved the file. It did not solve the problem. And when I tried many more new workbooks and got the same problem, I deleted my entire Office 2003 installation and re-installed Office 2003. It still did not solve the problem.
I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).
Option Explicit
Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function
Two requests:
1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?
2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
I need a VBA that will look through a spreadsheet in a specific range and clear any cell values of 0. The range would be Columns E,F,G,H,I,J,K,L all the way up to row 4000.
View 6 Replies View RelatedI am trying to find the solution for the problem I faced after downloading the "Color Filter" utility from this forum (Handle Duplicates & Color Filter)
I really do not understand why I was banned. I've downloaded utility from THIS forum and have kindly asked help on how to remove it because it does not meet my needs. I am not an expert in VBA and expected that somebody could help me. (It looks like the person providing the utility in the above indicated post shared the password as everybody is thanking him but I don't see where it is!)
I just want to remove the utility from my PC!!! And, if I can do this without bloody password than I do not want it! But, can somebody just explain me if there is another way to remove this from my PC or I have to reinstall EXCEL ?
I must say that I find it really increadible for beeing banned twice for no reason. Isn't it worse to place the password protected utility so that the one dowloading it cannot remove it anymore?
I attached a file in which column A is dr_cr and E id INST_NO and column G is INST_AMT. This file like a bank statement. in which one instrument(cheque) present and i denote it c(credit) in column A. but if cheque credit then d(debit) means that this cheque present and dishonour. but some time one cheque credit and then debit and then credit. it means that we have to remove previous credit and debit entries. in this attached file you found this type of entries. i want to remove this type of entries. i further explain.
1. if one instrument have one credit and one debit its ok.
2. if one instrument two credit and one debit then remove one credit and one debit where instrument no and amount and drawee bank must be same.
3. if one instrument have two credit and two debit we have two remove one one debit and one credit.
4. if one instrument have three credit and two debit then we have to remove two credit and two debit so one credit left.
Attached File : remove duplicate.xlsx‎
Is there a way to setup a spreadsheet so data that has been entered cannot ne deleted or edited, and only the last field in the column can be accessible? So if I have info entered in a1, a2, a3, b1, b2, b3, no one can edit or delete them, and can only add to a4 and b4? Will I need to write a macro for this or is it something I can do with certain settings?
View 9 Replies View RelatedAn apostrophe has appeared at the beginning of the words and/or numbers in the cells. I have tried using the "Replace" feature to remove these apostrophes and it won't work. I need to remove the apostrophes so I can upload our inventory, as the SKU must be pulled exactly.
View 3 Replies View RelatedThere are 2 sheets in the file attached.
-Data : Contain all the acc number and balance.
-Working : a sheet for my working to derive what I want . A string contain all the Acc Number
Bascially first I will paste and copy the acc number and balance from an external source to Data, then I wan to create a string of all the acc. This is done by the working from my Working Sheet.
In my Working Sheet, first It will take the value from the Data Sheet. Since the number of Acc / Balance may differ, I wan to know how to remove the 0 and #Num! for those null values.
Following that, I create a column sorting the all number from smallest to biggest, following by next column forming up the string. I do not want the same Acc to repeat in the string.