Free Microsoft Excel 2013 Quick Reference

Command Button Definitions

I want to be able to change the colour of a Toggle Button using VBA. Is there a definitive list of all the commands for each aspect of the button, i.e. for:

the Button Facethe Button Face when depressedthe Dark and Light Bevelsthe Text Label colour

Also, when the toggle button is depressed, there is a coarse pattern fill on the button face, is there a way to code this to be a solid shade?

So far I figured out how to code the 'button face' (which I can obviously do using the button's properties as well), but how can I control the other aspects of my button?

Private Sub ToggleButton2_Click()
    ToggleButton2.BackColor = 500 'this is red
End Sub
Many thanks...


Hi,

I have a working code on sheet1, however to make it easier (and nicer to look at!) I want to move the command button to sheet2. I have been through the code and where possible managed to specify sheet1 however there are a number of functions that still dont work such as "amend" - it looks for the active cell, which on sheet 1 would be fine...

I have been going round in circles for a while (being new to VBA!) and was hoping someone could point out the error of my ways! I think the problem lies with my definition of 'c'.

here is the code:


	VB:
	
 
Option Explicit 
Dim MyArray(6, 4) 
Public MyData As Range, c As Range 
Dim rFound     As Range 
Dim r          As Long 
Dim rng        As Range 
 
Private Sub cmbDelete_Click() 
    Dim msgResponse As String 'confirm delete
    Application.ScreenUpdating = False 
     'get user confirmation
    msgResponse = MsgBox("This will delete the asset record. Continue?", _ 
    vbCritical + vbYesNo, "Delete Entry") 
    Select Case msgResponse 'action dependent on response
    Case vbYes 
         'c has been selected by Find button
        Set c = ActiveCell 
        c.EntireRow.Delete 'remove entry by deleting row
         'restore form settings
        With Me 
            .cmbAmend.Enabled = False 'prevent accidental use
            .cmbDelete.Enabled = False 'prevent accidental use
             'clear form
            .txtdeponame.Value = vbNullString 
            .txtassetname.Value = vbNullString 
            .txtmaintenance.Value = vbNullString 
            .txtservicedate.DateValue = vbNullString 
            .txtsiteloc.Value = vbNullString 
            .txtassposi.Value = vbNullString 
            .txtassnumber.Value = vbNullString 
            .txtassdescrib.Value = vbNullString 
            .txtcond.Value = vbNullString 
            .txtcondnotes.Value = vbNullString 
            .txtfreq.Value = vbNullString 
            .txtassrespon.Value = vbNullString 
        End With 
    Case vbNo 
        Exit Sub 'cancelled
    End Select 
    Application.ScreenUpdating = True 
End Sub 
 
Private Sub cmbselectasset_Click() 
    If Me.ListBox1.ListIndex = -1 Then 'not selected
        MsgBox " No selection made" 
    ElseIf Me.ListBox1.ListIndex >= 0 Then 'User has selected
        r = Me.ListBox1.ListIndex 
        Dim strFind, FirstAddress As String 'what to find
        Dim rSearch As Range 'range to search
        Set rSearch = Sheet1.Range("a2", Sheet1.Range("a65536").End(xlUp)) 
        strFind = Me.ListBox1.List(r, 0) 'what to look for
        Dim f      As Integer 
        With rSearch 
            Set c = .Find(strFind, LookIn:=xlValues) 
            If Not c Is Nothing Then 'found it
                c.Select 
                With Me 'load entry to form
                    .txtassetname.Value = c.Offset(0, 4).Value 
                    .txtmaintenance.Value = c.Offset(0, 10).Value 
                    .txtservicedate.Value = c.Offset(0, 9).Value 
                    .txtsiteloc.Value = c.Offset(0, 2).Value 
                    .txtassposi.Value = c.Offset(0, 3).Value 
                    .txtassnumber.Value = c.Offset(0, 5).Value 
                    .txtassdescrib.Value = c.Offset(0, 8).Value 
                    .txtcond.Value = c.Offset(0, 11).Value 
                    .txtcondnotes.Value = c.Offset(0, 12).Value 
                    .txtfreq.Value = c.Offset(0, 15).Value 
                    .txtassrespon.Value = c.Offset(0, 19).Value 
                    .cmbAmend.Enabled = True 'allow amendment or
                    .cmbDelete.Enabled = True 'allow record deletion
                     
                End With 
            End If 
        End With 
    End If 
End Sub 
Private Sub cmbAmend_Click() 
    Application.ScreenUpdating = False 
    If rng Is Nothing Then Goto skip 
     ' For Each c In rng
     '    If r = 1 Then c.Select
     '   r = r - 1
     'Next c
    If Not IsDate(Me.txtservicedate.Value) Then 
        MsgBox "Please enter Date ", vbExclamation, "Date Required" 
        Me.txtservicedate.SetFocus 
        Exit Sub 
    End If 
skip: 
    Set c = ActiveCell 
     ' write amendments to database
    c.Offset(0, 4).Value = Me.txtassetname.Value 
    c.Offset(0, 10).Value = Me.txtmaintenance.Value 
    c.Offset(0, 9).Value = Me.txtservicedate.Value 
    c.Offset(0, 2).Value = Me.txtsiteloc.Value 
    c.Offset(0, 3).Value = Me.txtassposi.Value 
    c.Offset(0, 5).Value = Me.txtassnumber.Value 
    c.Offset(0, 8).Value = Me.txtassdescrib.Value 
    c.Offset(0, 11).Value = Me.txtcond.Value 
    c.Offset(0, 12).Value = Me.txtcondnotes.Value 
    c.Offset(0, 15).Value = Me.txtfreq.Value 
    c.Offset(0, 19).Value = Me.txtassrespon.Value 
     'restore Form
    With Me 
        .cmbAmend.Enabled = False 
        .cmbDelete.Enabled = False 
        .txtdeponame.Value = vbNullString 
        .txtassetname.Value = vbNullString 
        .txtmaintenance.Value = vbNullString 
        .txtservicedate.Value = vbNullString 
        .txtsiteloc.Value = vbNullString 
        .txtassposi.Value = vbNullString 
        .txtassnumber.Value = vbNullString 
        .txtassdescrib.Value = vbNullString 
        .txtcond.Value = vbNullString 
        .txtcondnotes.Value = vbNullString 
        .txtfreq.Value = vbNullString 
        .txtassrespon.Value = vbNullString 
         
    End With 
     
    Application.ScreenUpdating = True 
    On Error Goto 0 
End Sub 
 
Private Sub cmbfindassets_Click() 
     
     
    Dim strFind, FirstAddress As String 'what to find
    Dim rFilter As Range 'range to search
     ' Dim f      As Integer
    Set rFilter = Sheet1.Range("b2", Sheet1.Range("b65536").End(xlUp)) 
    Set rng = Sheet1.Range("b2", Sheet1.Range("b65536").End(xlUp)) 
    strFind = Me.txtdeponame.Value 
    With Sheet1 
         'Set c = .Find(strFind, LookIn:=xlValues)
        If Not .AutoFilterMode Then .Range("b2").AutoFilter 
        rFilter.AutoFilter Field:=2, Criteria1:=strFind 
        Set rng = rng.Cells.SpecialCells(xlCellTypeVisible) 
         
        Me.ListBox1.Clear 
        For Each c In rng 
            With Me.ListBox1 
                .AddItem c.Value 
                .List(.ListCount - 1, 0) = c.Offset(0, -1).Value 
                .List(.ListCount - 1, 1) = c.Offset(0, 3).Value 
                .List(.ListCount - 1, 2) = c.Offset(0, 7).Value 
                 
            End With 
        Next c 
        If Sheet1.AutoFilterMode Then Sheet1.ShowAllData 
         
    End With 
     
End Sub 
Private Sub cmbcloseedit_Click() 
    Unload Me 
End Sub 
Private Sub CommandButton4_Click() 
     ' insert today's date into the DateAdded box, with an "overwrite" warning
    Dim SDate As String 
    SDate = Date 
    txtservicedate.Value = Format(SDate, "dd/mm/yyyy") 
     
End Sub 
 
Private Sub UserForm_Deactivate() 
    Sheet1.ShowAllData 
End Sub 
Private Sub UserForm_Initialize() 
    Set MyData = Sheet1.Range("b2").CurrentRegion 'database
    With Me 
        .Caption = "Edit Asset Information" 'userform caption
        .Height = 501 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I want to do something VERY simple and because it's so simple, I can't seem to find out how to do it. Please help!!

All I want is a command button (or some other type of button, if a command button is not needed), that when you hit it, a pop-up window comes up. In the window, I'd like the title and the text to be pulled from different cells on a different worksheet (but still in the same workbook).

I want to put buttons next to terms with the term name on it, then the popup would pull the term name from a cell as the title (I could hard code this is this is a deal-breaker), and then pull the definition from the cell next to the cell with the term name in it as the text of the window(this is what's really important, I just want to update the definition once if needed). And then the popup would have an "OK" so they could close it.

I'd greatly appreciate any help.

Thanks!
Cori

Hello Everybody,

Relatively new to VBA and I'm having trouble with my user form. I'm attempting to combine two combo box criteria into a command button which will search my database for said criteria and produce corresponding values broken down by category. I've included an example which illustrates how I would like the final form to look and the code I have written so far (I have been successful with creating the two unique value combo boxes). I really just don't understand how to link that information with the command button and then the text box information below it. Can anyone help clarify this? Thank you in advance

I am having problems in making a command button invisible (worksheet, not form) after it has been clicked (Excel 2007). I tried this :

	VB:
	
 ShowAsGraph_Click() 
     
     'This is what the button should, and does, do.
    ActiveSheet.ChartObjects("Dashboard_Chart").Visible = True 
     
     'Now I want to hide the button to ensure it does'nt get re-clicked
    ActiveSheet.Shapes("ShowAsGraph").Visible = False 
     '.... but it stays visible.
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
without effect. I imagine the click-event is refusing to hide its own object because it is still executing.

Hi All- I am new to VBA so I do apologize if this is an extremely easy fix. I have a command button on a Worksheet which opens a UserForm (DraftInputForm). The problem is that the Listbox which is part of the UserForm will not populate upon execution of the command button. It will only populate after the "Clear Form" button is pressed. I understand it is not that difficult to press the "clear form" button but as I am making this for others I would like it to be as easy as possible. See my code listed below. Any help would be greatly appreciated


	VB:
	
 DraftInputform_Initialize() 
     
     'empty PlayerNameTextbox
    PlayerNameTextbox.Value = "" 
     
     
     'empty TeamTextbox
    TeamTextbox.Clear 
     
     
     'fill TeamTextbox
    With TeamTextbox 
        .AddItem "Marathon Man" 
        .AddItem "Nomar Mr. Nice Guy" 
        .AddItem "Fighting Saints" 
        .AddItem "Aubrey Hasselhuff" 
        .AddItem "ConanO'Brien's" 
        .AddItem "LeeHarveyOswalts" 
    End With 
     
     
     'empty AmountTextbox
    AmountTextbox.Value = "" 
     
     
     'Set focus on PlayerNameTextbox
    PlayerNameTextbox.SetFocus 
     
     
End Sub 
Private Sub CancelButton_Click() 
     
     
    Unload Me 
     
     
End Sub 
 
 
Private Sub ClearButton_Click() 
     
     
    Call DraftInputform_Initialize 
     
     
End Sub 
 
 
Private Sub OKButton_Click() 
    Dim emptyRow As Long 
     
     
     'Make Sheet1 Active
    Sheets(1).Activate 
     
     
     'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1 
     
     
     'Export Data to worksheet
    Cells(emptyRow, 1).Value = PlayerNameTextbox.Value 
    Cells(emptyRow, 2).Value = AmountTextbox.Value 
    Cells(emptyRow, 3).Value = TeamTextbox.Value 
     
     
End Sub 
 
 
Private Sub DraftInputForm_Click() 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to find a way to have a userform command button open a pre-defined listbox of items and have the user select an item and then submit to sheet1. What I am looking for is like 5 command buttons that will be placed on an image in the userform. The user will press a button and it will open a small listbox of say 5 items to choose from. The selection will then submit to sheet1. The part I really know nothing about is how can I attach the listbox item in sheet1 to the command button that was pressed.

Sort of like this:

Userform =
Button1
Button2
Button3
Button4
Button5

Listbox1 =
Orange
Blue
Red
Yellow
Green

Sheet1 cells would show after selection:

A1 = Button1 B1 = Orange (button 1 pressed and orange was selected)

Any ideas how I can do this? Any help is greatly appreciated. Thanks

Hi there,

I want to get some simple VBA code that will paste the text written in various text boxes in one sheet into a single cell in another sheet using the command button. The idea is to have pre-populated text boxes (which can be amended or changed) which can be amassed into a single document.

I have attached an example which should explain what i mean but i am absolute VBA beginner.

Any help will be much appreciated.

Thanks, Peter

Hi folks. I don't know how to make a Command Button visable only between specific dates. This Command Button will contain code to update members in my Volunteer Fire Department. Thanks for your help
Keith

Hi all!

I have a spreadsheet with a map on the first sheet. On this map I have put in autoshapes in the form of rectangles which I have then linked to their relevant sheet about that region. I also have a command button on this sheet that when you click you create a new sheet for you to fill in about a new area.

I was wondering if it was at all possible when you create a new sheet you also create a new rectangle which the user simple has to click into place onto the map? i.e. the sheet is created and then the user is prompted to click where the concerning region is on the map aand once the click is made a rectangle is created at that position. This rectangle contains a hyperlink to the new sheet.

I have no idea if this is possible, however in worst case simply creating a rectangle for then the user to manually drag into place (like we normally do) is also ok.

Any help is very much appreciated! Thanks!

I would like to open a pdf file click on the command button. I have developed a tool now want to add another button for "Help". if the user clicks on it, the PDF user manual will open.

Hello,

I have a command button and assigned macro on it. Macro is created to get a username+date&time on the Worksheet. When I click the button I get a username, I am not allowed to change the username as the cell is protected but I can again click on the button and the date or time will automatically change. How should I protect this button (by macro) that will not allow anyone to click on it for the second time?

Thanks for help!

I have 40 sets of up to 10 command buttons which I use to Navigate around a large sheet of data. I decided to use a form control command button rather than an ActiveX command buttons because I did not want 400 commandbuttonx_click() procedures. I added these buttons via VBA at run time since their location is dependent of the data I use (which is different each time I open the workbook - data is obtained from external source). I realise that form command buttons are only a part of VBA to support older code and Microsoft have developed ActiveX controls to replace them. If I have to I will change to an ActiveX control.

My code:

	VB:
	
 pButtonHeight) 
     
    Dim j As Integer 
     
    For j = 1 To pNumofButtons 
        ActiveSheet.Buttons.Add(pDistFromLeft, pDistFromTop, pButtonWidth , pButtonHeight).Select 
        With Selection.Characters(Start:=1, Length:=3).Font 
            .Name = "Calibri" 
            .Size = 8 
        End With 
        Selection.Characters.Text = pCityLetter & "R" & j 'NavButtonName
        Selection.OnAction = "GotoSite" 'define procedure which is called when button pressed
        pDistFromLeft = pDistFromLeft + 30 
    Next 'j
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want to change the background color of the buttons depending on their location (using 5 different colors, one for each of 5 cities)

To date I have not been able to find a property(?) with which I can change the background colour of the button.

I would assume that it is related to the Shape properites.

Regards
Tel

Hi Guys,

Please I need help with vba codes for the following tasks:

I have a Master Invoice Sheet and wish to place three command buttons on the sheet as follows:

1. Command button 1 – when clicked, to automatically generate an Invoice Noumber say in Cell C13.

2. Command button 2 - to have a code that will save a copy of the invoice as "Save As" with the Invoice number in Cell C13 itself being the name of the file, and then clears the data to get ready for the next invoice number to be generated.

3. Command button 3 – to have a code that will clear the Invoice and save the WORKBOOK as the Master Invoice.

I’ll be very grateful for any help with vba codes for the three buttons specified above to be placed on our Master Invoice sheet.

Thank you for your anticipated help.

Buddy

hello,

i have a command button but i want it to run by pressing only (-) minus sign?
is there a code in this problem?

thanks

More a syntax question than anything else, Im adding graphs and summary source data worksheets that can be requestd via command buttons; however I want it to ignore other summary source data sheets, this in itself is not an issue


	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
* GD is short for graph data. I created worksheets as often it needs to hold 3-400 data points per series to be graphed, although I maybe under the wrong impression that graphing straight from an array is limited to 80 unique points

However as you can see its getting very long- is there way to shrink this down?

Dear helpers,

I have a command button in my workbook (see attached) that I want to be disabled when a cell value reaches 910 (it's the one marked "Click to submit report" located near column TU row 3). At the moment I can achieve this using this code:


	VB:
	
 Range) 
    ActiveSheet.CommandButton4.Enabled = Range("P6") < 910 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The user selects the comments using the checkboxes in column AA and the text comments in columns P:Z appear in the report field P7. The character counter is in cell P6 and it counts what is in P7. That is the event that triggers the disabling of commandbutton4. But it doesn't disable the moment the cell count exceeds 910, you have to click somewhere else first.

Is it possible for that button to be instantly disabled without needing that extra click?

One other problem I have is that the code I quoted above generates an error when the user clicks on the hyperlinks in cells A9, A10 and A11 (don't try the other hyperlinks, I haven't set them up yet). The code is not mine, I copied it from somewhere else. Could someone please improve the code for me so that it doesn't cause this error?

Thank you very much!

Nicholas

Hi,

Thanks for looking at my post..!

Quick question..

If I need to clear data that is on another tab in a worksheet, and I want to clear this from a UserForm Command Button ("CLEAR"), what is the code for the Clear button to do this?

Thank you very much!

I have a "View Only" command button that i need code added to. I would like to add a timmer to the button that returns the user to the home page after say 30 seconds. I dont want to apply it the whole book as i will be changing sheets often, just the sheet i select from the "View Only" button. Witch could be 1 of 150-200 sheets. I dont need 200 sheets coded just the button to apply a timmer 1 time for any sheet i choose. Shouldnt be to hard for you guys Use the admin account at log in. and the password is admin also.Equipment PMP V3.0.zip Please let me know if further detail is needed

I saw a post like this but I cannt find it.I hope someone will help me with a code for a single command button.thank you .

Hi,

Hope everyone had a great holiday. I have been attempting to teach myself user forms over the weekend, and got pretty
far with a practice form. Right now, I've been struggling a bit adding the macro for a command button to show the user form. Just can't seem to make it work. If anyone has a moment, can they check my attached sheet to see if I'm missing something really simple?

Thanks!

Hello Guys,

I have a userform with a textbox and a command button. What I want to do is when CommandButton1 is clicked, excel will look for duplicate values on Sheet1 based on what's in TextBox1. If it finds a duplicate value, a pop up message will then appear.

I hope someone can help me with this.

Thank you in advance,

JC

Hi, I am attempting to create a command button using a module in excel as part of a project, and every time I do I get the same runtme error '9' subscript out of range error, but the buttons are still created and titled and colored appropriately. I have been working on this for hours and need some outside help. Thanks!

Code:


	VB:
	
) 
     '
     ' Creates a Command button and Positions it
    Sheets(name2).Select 
    Dim oOLE As OLEObject 
     ' Add a Command Button
    On Error Resume Next 
    Set oOLE = Sheets(name2).OLEObjects.Add(ClassType:="Forms.CommandButton.1", DisplayAsIcon:=False, left:=left1, top:=top1,
Height:=24, Width:=90.6) 
    oOLE.Activate 
     ' Change Color
    oOLE.Object.BackColor = color1 
     ' Change caption
    oOLE.Object.Caption = caption1 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


How do i format a Command Button so that it runs another subroutine for a database query , automatically resets and then when i press it again it clears the past data and performs the query once again?
Thanks for your help in advance!

Windows Office Excel 2007. When I use ActiveX Command buttons in my sheets, I continue to have an issue with usually one of the command buttons, resizing on it's own (Getting bigger) after switching from an external monitor to my laptop screen (a resolution change happens). Oddly enough, it is usually the first button that was placed on the sheet. I've checked the button settings and they are all the same, but the resize only happens to one button. The buttons were created by inserting the first ActiveX button and then copying and pasting to add more buttons. This has been repeatable on several worksheets/workbooks. The only code on the button is a .click routine that calls a procedure.