Add Icon To Macro Button Using VBA
The following code is for a macro button I have setup, but it is in text only form. I want to add an icon to the text. Also, are there other icons to use than the lame ones from Microsoft?
Dim myButton As CommandBarButton
Set myButton = Application. CommandBars("Worksheet Menu Bar").Controls.Add
myButton.Caption = "Hours Entry"
myButton.Style = msoButtonCaption
myButton.BeginGroup = True
myButton.OnAction = "Show_"
View Complete Thread with Replies
Related Forum Messages:
Button Icon Folder Location
I am running WinNT and need to find the folder containing the default icons available for use on buttons in the toolbar window. I can copy the image but it saves as a picture (device independent bitmap). I want to be able to send the icon to other people so they can put it in their directory and choose to select it.
Add Button To Sheet & Assign Macro Code
how I can write code to create a button on a worksheets and then link to another subprocedure.
I tried recording a macro and this is what excel so eloquently gave me:
ActiveSheet.Buttons.Add(384.75, 60.75, 79.5, 39.75).Select
Selection.OnAction = "CreateImport"
Selection.Characters.Text = "Parse Deposits for Import"
With Selection.Characters(Start:=1, Length:=25).Font
. Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
This works fine, my only problem is if the button that is created is actually named Button2 or 3, etc. Is there a way to assign the new button to a variable, or would it be an object? this way it wouldn't matter what it was named.
Assign 2 Macros To 1 Button (or Adding VBA Code To The Existing Macro)
I'm verrry new to editing VBA code and I've been trying to figure out how to send a column of data ("Min Data" AB3:AB21 from "Sheet1") to a worksheet ("F4" - Min) and sending another column of data ("Max Data" AC3:AC21 from "Sheet1") to a different worksheet ("F4" - Max) by using only the one button ("Record Data" from "Sheet1"
I've been trying to figure out ways to edit the VBA in "Module 1" to make this work
Can anyone help me out here? If what i'm asking for isn't clear enough, just let me know and i'll try to explain in more detail.
Add Button If No Button Exists Already
Is that each time the user clicks the button on the first worksheet a button is added. And the buttons begin to multiply like bunnies. I have been trying to find a way to have the macro check to see if a button has already been created and not create another if one is there.
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
'If ActiveSheet.OLEObject.Name "Click here to print" Then
ActiveSheet.Buttons.Add(500, 1000, 75, 40).OnAction = "PageSetup10"
Last Button And Add Button On Userform
I have created a userform for users to enter data and can not seem to make it work correctly.
I am having a issue on the Row Number Textbox. I am trying to get this textbox to reflect the current row number that I am viewing with the Userform.
I am also having a issue with the Last and Add New button.
I have formulas in Column A and Column H and when I try to go to the last Row or Add a new record it recognizes the formulas and it takes me to the row below where the formulas are. (Example Row 601 instead of Row 3)
Macro Works With Form Button But Not Command Button
This is probably really straight forward but cant see why it happens, the following macro works fine when called by a button created by the form toolbar but doesnt when called by a command button, get the runtime error 1004, "select method of range class failed"
Vba Calculator: Click Add After Inputting The Digits Just To Have It Added To He Variable, Which I Just Add Up At The End
i have 1 - 9 number buttons, the +, - , * , / , and a C for clear, as well as an exit button. the display is a label. i have to click add after inputting the digits just to have it added to he variable, which i just add up at the end... terrible.
Option Compare Database
Dim var1 As String
Dim var2 As String
Dim first As String
Dim second As String
Dim sign As String
' on load
Private Sub form_load()
Form.Caption = "this calculator lab sucks."
Me.lbDisplay.Caption = ""
' on clear
Private Sub cmdClear_Click()
Me.lbDisplay.Caption = ""
var1 = ""
var2 = ""
Running The VBA That Is Assigned To Multiple Cmd Button On One Cmd Button
I have a workbook that contains 18 sheets, on 14 of the work sheets I have a comand button that runs the following
Private Sub CommandButton1_Click()
Dim nloop As Long
For nloop = 3 To 53
If nloop 3 And nloop 12 And nloop 15 And nloop 16 And nloop 23 And nloop 28 And nloop 41 And nloop 47 Then Range("K" & nloop).Value = 0
Range("B2").Value = DateAdd("d", 7, Range("B2").Value)
I want to put a comand button on sheet18 that will run the code so it does the same thing as pressing each command button on each sheet.
the sheets that I have the cmd on are:
Sheet1, Sheet2, Sheets3, Sheet4, Sheet5, Sheet6, Sheet9, Sheet10, Sheet11, Sheet12, Sheet13, Sheet15, Sheet16, Sheet17
Add A Sort Button
I'm trying to add a sort button to a list where I choose which column to sort. Attached is an example. Can this be done without VBA? If not, is the VBA easily editable??
Button To Add Row
What I want is a button, that, when pressed, adds a row to the bottom of a set of data. To clarify, see the example spreadsheet here. When the "Add Item" button is pressed, a row should be added when row10 is now, but still leaving a gap between that and "Summary of the data". I am using OpenOffice.org Calc; I am not sure if that makes any difference VB-wise or not.
Add A Remove Button To Form
I have a userform that adds and deletes employees. The add button works but im not sure how to get the delete button to work. I have a defined name of "Employee" that is row "A". What I would like to happen is that when a employee is selected from a userform list and the delete button is pressed the employee is deleted and the form is updated.
Add Shortcut Key To A Button
When i have an excel sheet opened and hit ctrl +f the find replace window displays. I want to add a button on spreadsheet that when i click on it, the find and replace window appears, I can not figure out how to do this. I tried using .onkey
Can I Add A Submit Button To Worksheet
I'm working on an order form in excel and wondered if someone can tell me if it's possible to add a 'submit button' to a worksheet (order) that would do the following,
1.) print 3 copies (orders) of the current sheet
2.) create a new worksheet in the same workbook with a new order number (a specific cell in the worksheet) one higher than the current one. Also naming the new worksheet with the new order number.
3.) Save the current workbook.
Maybe I'm getting a little ahead of myself here but any help would be much appreciated as I'm really rusty at this...
Add Button Caption To Cell
Is there a way to program a VBA button so that its cell location is known. For ex., I have a button that says "Fail". I'd like to program it in such a way that when the user clicks on it, the text "Fail" is inserted into the cell next to it. How do I avoid hard-coding the cell reference?
How Do I Create A Button In Xl07 Ribbon For Add In
I have a workbook that has a userform with some simple code to translate formulas from .FormulaLocal to .Formula, and back.
I use that workbook a lot to translate formulas I find on here to something my German XL2007 will understand. I thought it would be neat, as well as giving me the opportunity to learn something new, to have that functionality as an add-in, rather than having the workbook open all the time. It is easy enough to save the wb as an add-in, and I can see it in my add-in list, install or un-install it. What i can't figure out is how to put a button on the xl07 ribbon when the add in is installed so i can access it.
Creating A Custom Button When I Install My Self-made Add-in
Finally after 3 days of intensive studying VBA, my first add-in pack is ready to be installed in the office. I've got my custom form all ready, now all I need is to have excel create a custom button on the toolbar that calls up my custom form when clicked.
The idea of assigning a shortcut key doesn't really appeal to me. I'll be packaging it up in .xla format. Would be greatful if anyone can direct me on how to add a button to call up my form (only one form in fact).
I'm using the bellow code which I think Igot from this site, to change the excell icon to my own, Only thing is that in the top left hand side of the workbook still says: "Microsoft Excel - My File Name". Does any of you fine people know how to get rid of the Microsoft Excel and show only "My File Name"?
Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Integer, ByVal lParam As Long) As Long
Declare Function ExtractIcon Lib "shell32.dll" Alias "ExtractIconA" (ByVal hInst As Long, ByVal lpszExeFileName As String, ByVal nIconIndex As Long) As Long
Const WM_SETICON = &H80
Dim lngXLHwnd As Long, lngIcon As Long, strIconPath As String
strIconPath = "My Path:MyIcon.ico"
lngXLHwnd = FindWindow("XLMAIN", Application.Caption)
lngIcon = ExtractIcon(0, strIconPath, 0)
SendMessage lngXLHwnd, WM_SETICON, False, lngIcon
New Command Icon
I'm looking to have a doc where people time stamp their start and stop times. I have found that Control(apple) - Shift -semi colon works. The problem i'm having is that I want people to be able to use a command icon in their toolbars for this task as opposed to people have to use the keyboard. I can use a button with a macro but this is not possible as our excel corrupts when there are too many macro's
Change Desktop Icon
I was wondering how I could change my desktop icon for one specific workbook to something other than the excel icon. I saw the below link (2nd tip) but that changes the icon for every file of a specific file type. I just want to change one specific file's icon on my desktop to something custom, like a clipart picture of my choosing. I also know that this can be done with shortcuts, but I need it to be changed on the original file. I tried using the IconChanger program, but the icon stayed with the file name so if I did a "save as" or emailed the file, it disappeared. I want it to stick with that file for good, or any file I duplicate from the original, and work on other computers as well.
Macro Doesn't Work On Button, But Fine From Macro Menu!
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.
When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.
The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.
Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!
User Programable Icon On Toolbar
I have an application macro which displays a Userform which lists and lets me select/load my favorite XL WBs. It also lets me add and/or delete favorites.
At present, I have to activate it with a Ctrl+Key combination.
Is there anyway I can assign it to an icon on the toolbar.
Change Desktop Excel Icon
I was wondering how I could change my desktop icon for one specific workbook to something other than the excel icon. I saw the below link (2nd tip) but that changes the icon for any file of a specific file type.
I just want to change one specific file's icon on my desktop to something custom, like a clipart picture of my choosing. I've seen it done with certain companies changing the internet explorer shortcut icon linked to their webmail, changing it to their logo.
ActiveX Comboboxes Change To Icon
I have one sheet with about 100 activex controls (and another few 100 on the other sheets in the file), most combobox, but some checkbox as well. There are 30 of them lined up, one in each row. One at a time, one control will basically change to an icon only. If you select it in design mode, the name shows in the range name area of the toolbar, but when you select properties you get the properties for the sheet. Effectively I have an icon with nothing linked to it anymore.
For a while, it seemed to be somewhat random. But for the last few days, it is now cascading down. First combobox #6, then #7, etc. I'm now on #11 or so. Seems to happen when you save then reopen the file, not if you just keep working in the file without closing it.
The macros behind the boxes are still there and run once I recreate the missing box (normally with an Alt-D, and editing the properties; but I've also created from scratch from the toolbar, and used alt-C, alt-V) they work fine. I've run a compile, with no change. Any thoughts on finding the root cause? I thought it might be from blowing up a macro, but I've tried it without even editing a macro and it has the issue.
Creating A Macro, That Creates A New Button, Which Itself Runs A Macro..
I have a long complicated macro that processes a ton of data and gives the output in a new sheet. I then want to create new buttons (or some sort of user clickable triggers) on this new sheet that runs another macro.
I have got the actual adding of buttons in the sheet working, but I can't find a way to automatically assign macros to newly generated buttons during runtime. Is this even possible (or are there any clever tricks I could use to get around this?)
How To Do Pull-Down Menu With Colored Graphics Icon?
I'm trying to do a pull down menu with three selections - green,yellow, and red dots? I set up the data validation and referred it to a list. That is under Data Validation -> Settings -> Allow: List; Source: a range of cells where I put 3 colored dots(graphics). Now in the pull down menu when I select the first choice, it just gives me a black dot. The second choice gives me the same black dot. And so does the 3rd choice gives me a black dot.
So, how do I carry the 3 colored dots over? Or it's not possible using the Data Validation approach? If not what is the best and easiest way to go about doing this....
Rename Word Object File Icon
I've added an word object to my excel sheet as an icon. The only problem is that it shows the word icon then under it says 'Microsoft Word Document'. This might be an obvious and silly question. But how do I Rename the 'Microsoft Word Document' to a title of my choice?
Button Color In Vba
I am creating a button using the following code
With ActiveWorkbook.Sheets(ASheet).Buttons.Add(280, 30, 160, 30)
.OnAction = "CODING"
.Caption = "MAIN MENU"
.Font.Size = 25
.Font.Underline = xlUnderlineStyleSingle
.Name = "CODING"
how do I adapt the coding above to allow me to change the background of a button.
the button above ideally will have a white background and appear invisible, ie a hidden button
my other buttons will be assigned a default colour of light blue and change to a different colour when selected
Anyway To Create Shortcut Icon To Open A Specific Worksheet?
Is there anyway to create a shortcut icon that I can place on my windows Desktop - That when launched will open a workbook to a specific worksheet/tab. Has to be a windows shortcut as the workbook is auto generated eachday and sent to me, so if I add vb code it will not matter as I will get a new workbook the next day thus wiping out the vb code.
The person who compiles it leaves it on a certain tab that I dont need, but is commonly used by other people - so I would like a shortcut to skip that worksheet and take me to the worksheet that I need to use.
Command Button VBA Code
So right now I have the
Private Sub CommandButton19_Click()
Dim product As Double
product = ActiveSheet.Range("J45").Value * ActiveSheet.Range("S44").Value
Sheets("Sheet3").Range("R2").Value = product
I want to be able to switch the cell the product goes into based on a certain command button that I click(for example I have a command button for when I use copper wiring and one for aluminum wiring and I want when I click the copper wiring button for the product to show up in one cell, but when I click the aluminum button for the product to show up in a different cell , but on the same sheet). To be more clear I have a quantity command button(which is the code above) that i want to be able to recognize the difference between when the aluminum command button is clicked and when the copper command button is clicked.
VBA Volume Turn Button
I would like to integrate a button on my spreadsheet that looks and works much like a round volume button on a stero that you can turn in order increase or decrease volume.
However, I would like the button to control the values of a cell. If the volume is turned down, it should display a 1. If the volume is turned up, it should display a 16545. In between you should be able to - more or less - tune in on any integer in between.
Changing Button Attributes In VBA
I am using VBA to change the attributes of Command Buttons on a number of pages and due to the numbers involved wanted to automate the process.
Unfortunately whilst I can select the buttons, changing the attributes I cannot get to work, e.g. For Command buttons CB1 and CB2
Editing Button Image With VBA
I'm designing a custom toolbar in VBA and have managed to create a toolbar with buttons.
However, I've been battling away trying to edit the buttons images so that I can design the button image to match the nature of macro assigned to it. The problem is that if you record yourself doing it, the macro written in VBA doesn't seem to recognise the editing button actions.
Does anyone out there know of a way to custom edit a button image in VBA?
Custom Floating Toolbar Created - Icon And Size Questions Remain
I have created a custom floating toolbar that works perfect (thanks to all the reading on here!). My workbook is a stand alone estimating tool that many users will have a copy of.
I have two things I want to do yet but I cant find it on here.
1) My buttons on my toolbar - I want to have custom icons. I can do this temporarily but when I exit my worksheet and re-open they are lost. Is there anyway to save a custom icon. I am willing to add it to the icon list if necessary and then when users open the workbook copy the custom icons onto their computer. But I am not sure how/where I could do this.
Worse case, can I color my button text?
2) When my toolbar appears, it is wide, all buttons are going left to right. I want the size to be small width so the toolbar buttons are on top of each other. Any thoughts? I tried adding a .width but it didnt work.
Protect / UnProtect All Workbooks With A Single Button Through VBA
I am having some 60+ workbooks in which the end-users are entering data. Each workbook is protected with different passwords.
We have the systems networked. The main compilation file is in the server D:MAIN folder. The workbooks for users are in D:Mainusers sub-folder. The files in users folder are only allowed to have access by the users.
Now, in the compilation sheet I have linked the data from all the individual files (which is in its subfolder). I am facing a trouble that the excel asks password for each and every workbook to update the links in the compilation workbook.
Any possibility to overcome this trouble? A VBA code is the solution? or any other alternative measure is there to solve?