I have written a macro. The macro in question simply shows a user form. There is far more code within the user form itself. It is designed to automatically format a workbook.
I saved the macro as an .xlam add-in file to the default folder. I then added a button for the macro to my Quick Access Toolbar. This seemed to work great.
However, when I open a new file that I want to run the macro on, the button disappears. In fact, a 2nd Excel window opens up on my task bar. The original window still has the macro button, but the new window does not.
I've created some macros in Excel 2010 which I've added to the Quick Access Toolbar and want to show them using some of my own icons rather than the ones that show when you click the 'Browse' button. There is no option to navigate to any other location.
Is it possible to access icons other than the set provided?
When adding functions to the quick access toolbars in Excel 2007 sometime they do not have an associated icon with the function. The icon shows as a green circle. How to change the icons and also to have a selection of icons like you have had in previous versions of Excel?
I would like to create/use a copy command in the Quick Access Toolbar that combines Paste Values and Transpose into a single command. Have tried recording a macro but it becomes specific to the individual workbook that I'm accessing and it copies the formula from that last cell that I copied from. Problem is that I have 19 workbooks to deal with and would like to save some keystrokes.
I need to send out an order form (spreadsheet) to 100's of people that need to complete the form and email back to me as an attachment. If I was completing the order form myself I would use the "email" icon that I have pinned to my Quick Access Toolbar (QAT). However, most of the recipients don't even know the Toolbar exists.
Is there a way I can insert an icon / hyperlink in the spreadsheet that does the same thing as the QAT icon. I can insert text to say "click here to email your order" (or similar).
I need to keep it in an excel format and an icon is so much better that asking them to save to their hard-drive and attach to an email, etc.
The QAT icon is exactly what is needed but I need to provide a spreadsheet that works for folk who haven't got the icon.
I've developed a fairly simple procedure which lets me reformat cell backgrounds in a worksheet after various cut/paste operations.I've created a template called BASIC.XLTM which has this code in it. I've added a button to the QAT to enable me to invoke the procedure, but when I click the button to do the reformat, it works for the active sheet, but it also invokes another instance of BASIC.XLTM.
I've created a custom toolbar via Excel's toolbar wizard, to which I have added several macro buttons. However, when clicked, instead of running the assigned macro, Excel instead attempts to open my already open file?? I think I read something about having to manually change the buttons to refer to a .xla ??? Not sure though.
I noticed that my custom "Close without Saving" button stopped working. I checked the VBA editor and my Personal workbook was missing and all of my personal macros were gone. I restarted excel, same issue. Rebooted my machine and they re-appeared. Not sure how what happened, but at least the Personal workbook was not lost. I'm working off my company's network, so often I get the "Personal Workbook is locked for editing" message. I usually select the open as read-only file and go about my merry way. Perhaps I clicked something different on accident without paying attention.
Anyway, I digress, the custom macro button that I had in my ribbon still didn't work, so I removed it and tried again in both the ribbon and the quick access toolbar with no success. The Macro executes if I execute it manually or via the keyboard shortcut, but the ribbon/quick access toolbar buttons won't work. Frustrating, because I use this quite often. I've even tried writing a quick "dummy" macro to see if it was some sort of code error related to the macro, but got the same result. My "Close without saving" code is below (super basic code).
Sub Close_Without_Saving()'' Close_Without_Saving Macro' Closes active workbook without saving changes.'' Keyboard Shortcut: Ctrl+q' ActiveWorkbook.Close (False)End Sub
I have finally come up with a macro and would like to install it to around 10 people's "Personal Workbook" in my department. Let me explain a bit further...
We use web-based software which has an "Export to Excel" option which we all use. The resulting data populates into a spreadsheet automatically.
I would like the user to be able to click on Tools/Macro/Run Macro and then run that particular macro. I assume that this macro should be in the "Personal Workbook" so that the macro will be visible no matter what worksheet they may have open?
Can this be done automatically/with a macro or must this be manually done?
I have read several articles saying using a command button as a toggle button can't be done but some articles have said it is possible using the state properties.
.State = msobuttondown .State = msobuttonup
Is there any way to combine this with the onaction property so that when you press the button, it stays down and activates a macro then when you press it again it pops back up and activates another macro.
I am running Excel 2003 and in my workbook I created a macro and assigned it to a toolbar comman button, "Copy Filtered Data". This macro copies the visible data into a new workbook.
This works well but others in my company will also be accessing this workbook from a sharepoint and I discovered that when they open it this command button is not on their toolbar.
Would anyone know what I need to do to have this button load on the toolbar when the workbook opens?
Also, I would like this button to either unload or greyout when the workbook that this button is intended for is closed.
I have created a toolbar button to save a file as a specific name based on a cell value. The macro runs fine, but the button changes priority when the new file is saved.
i.e. the original file is called "quoting_tool" and the newly created file is called "quoting_tool_'B3'". However, the next time I click this button in the original file, it requests the new file as the home of the toolbar button. I have tried saving the original file after adding the button, but that doesn't work.
When I hit the print button on the Standard Toolbar, I want my printer to print only the number of pages specified on my worksheet which I previously entered into Cell A1.
Is there a formula or something I can use for this?
I am trying to assign a macro to the Print button on the Standard Toolbar. Basically I want that button to print the number of pages I've specified in a cell, but I still want to be able to use the 'File/Print...' option if I need to.
I did try to solve this issue in an earlier post, but this exact problem remained unsolved. For more information
I am looking to create a form button in Excel 2010 that runs a .tto file. I have an add-in for Excel 2010 called "Transfer Data From Iseries" The .TTO file is a definition file of files, SQL, etc. What I need is a button on the screen (I do not want to try to explain to 1K users on how to add an add-in in Excel. When you click on the button it should open the transfer dialog box and then they put in a userid/password and it populates within that Excel file.
I tried to record a macro to get the script, but it does not record this information. I also tried to use Insert > Object in excel and it works, except that it does not open the data into that specific excel file (the one that you are in).
I am using the following code to create an Excel toolbar custom control Button. What would be the VBA code to change the color of the image I have chosen? ....
I created a custom toolbar button which works perfectly.
Because I needed to save each workbook as a specific workbook this is taken care of by the vb code.
I would like when the button is pressed for the original workbook template to be opened.
At this moment if I have a blank workbook or no workbook open and I press the button the programme opens a version of the workbook but not the template version how do I point Excel at the templates.
On a sheet, I have a Worksheet_change sub that checks if the cell changed is in column 1, 10, or 19 and if it is, then the six cells to its right are filled with formulas I need. All formulas work, except for one. I've checked over and over again and the formula itself works fine - the coding behind it is exactly what it's supposed to be.
But for some reason sometimes the formula isn't put into the cell. I haven't found any pattern yet with this problem. Even stranger is that the formula is identical to one in another column before (which is also entered by the macro) and the other one has never disappeared yet. I'm setting both formulas with the abc.FormulaR1C1 property (I don't use the abc.Formula property to avoid having to write three different formulas - one for each possible column change).
Image size appears in button(msocontrolbutton) of custom toolbar Excel 2000 is very small and blurred using below code. How I can increase size of image in button (msocontrolbutton) as well as brightness of image in custom toolbar? I am able to increase size of button using width and height property but image appears in center of button as 16X16. How to fit image to button(msocontrolbutton)?
Const cImgCommandBarID As String = "TMC Img Toolbar" 'Image code Dim cbImgBar As CommandBar
Set cbImgBar = CommandBars.Add( Name:=cImgCommandBarID, Position:=msoBarTop)
'cbImgBar.context = 1000 Dim cbImage As CommandBarControl Dim imgTool As Shape Dim sFileName, ImgSheet sFileName = ActiveWorkbook.Path & "ImagesABC.jpg".....................
I'm working on a sheet with about 10,000 rows and 8 columns worth of data. Most of them are formatted as 12345,12345,12345,23456 how they are supposed to be, but some of them will have spaces inbetween each sequence, or some even a couple spaces, or spaces at the end. For the most part I can use the replacement function with ", " to "," but some of the double spaces throw it off, and end spaces also. Is there a macro that can just search through the selected cells I pick to just remove all spaces so the data falls back onto the commas?
which of these toolbars provides the better 'controls' to paste onto worksheets (as opposed to UserForms)? For those wondering, both toolbars contain some apparently similar controls, e.g., combo box, radio button, spinner etc. but there are differences in their behaviour it would seem...
I am writing a macro to automate the filling up some data from excel to company website. I have changed the website name in this post for data protection purpose. The excel will login for different clients by using the combination of username and password for respective clients and then some data are required to be inserted in a text box on a web page, I think the text box is on a form and form is within an iframe, within the web page. Once the data is inserted into text box, one button (Submit), which is also on the same form, is to be clicked.
On the click of a button, the updated data appears on another section, I could not make out if it is an form or frame, which is under the abovementioned form. Once we are happy with the way data appears on the web page, we have to click another button (Update), which is on the same section, to finally updating the data on website.
I wrote the following code to login to the website and then to navigate to the web page where I have to fill up the performance numbers in a text box.
The first problem is how to access the text box inside the form from VBA so that the macro can write a number in that text box and how to access the button to submit the data. The HTML code, which can be seen on click of F12, is attached below.
The second problem is how to access the Update button inside the other section, so that the data will be finally uploaded.
VB: Sub LoginToCorpAccount() Dim ie As Object Set ie = CreateObject("InternetExplorer.Application")
What I'm trying to achieve.I'm trying to create a toolbar to have visible in excel which provides me with shortcuts to a selection of functions and some other useful documents.
I've created a document "Toobar 1.xls" in column B of which I have the file locations of the documents I want to open, the documents are moved from time to time and it is easier for other users to update the spreadsheet than vb.As you can see below I've written a piece of code to open up a document, I've created a function because there are a few documents I want to create links to and thought it would be cleaner to do it like this. This code is stored in "Module1" of "Toolbar 1.xls".
I've then created a new toolbar in excel and added a custom menu item and allocated it the macro "Pension_Credit_Calc".
When I run the code from within "Toolbar1.xls" it works fine, however if I try to run it from another workbook I get "run time error 1004". I suspect it's probably something to do with where I've stored my code,
PS. The reason I have not simply created a shortcut in the quicklaunch bar is that I will also be altering the workbooks once opened.
Code.
VB: Function OpenUp(FileLocation) As String Workbooks.Open (FileLocation) End Function [code]....
I have created some macros that need to be used in several workbooks by more than one users, so I put them in an add-in and gave them instructions on how to get the add-in. I also created a toolbar with buttons calling those macros, attached it to a workbook the users can open, and instructed them to open that workbook once to get the toolbar.
Both the add-in and the toolbar workbook are on a network drive that my users can access, but when they get the add-in, it gets copied to their own add-in folder.
Here's my problem: When a user tries to click on one of the buttons in the toolbar, the button tries to call the macro using the path that would be valid on my computer to get to my add-in folder. This path includes my user name, so it doesn't go to their own add-in folder: it goes to my add-in folder on that computer if I ever logged in on that particular machine, or it goes nowhere.
When I created the button, I attached the macros by referring to the add-in workbook by name only, without referring to a path, so I don't know why MY path ended up defined on that toolbar.
How can I go around this? I manually relinked all the buttons for that user, and I can do it for the other users (there aren't that many users nor that many buttons), but I'd rather avoid it: I'll have to re-do it if the toolbar is updated and it just seems like more work than should be necessary.
I'm also opened to suggestions on a better way to share those macros. They can't be attached to the workbooks because we use new workbooks every year - they're emailed to us by the people who create them and for a variety of reasons, pasting everything into a workbook of our own is not a good option.
