Free Microsoft Excel 2013 Quick Reference

Possible to hotkey/underscore a VB button on a worksheet?

Hello,

Is it possible to hotkey a visual basic button on a worksheet? Like in MS
Access you place an ampersand (&) before the character to hotkey

&Run Report

would underscore the R in Run. So when you press Alt-R that would activate
the button that says "Run Report". Is this doable on a button in a
worksheet? How to do this?

Thanks,
Rich


Is it possible to conditionally format a cell based on the value of another cell?

I am trying to organise historic data of a horse racing system I am working on, and I have a column of finishing positions. Obviously if the position is 1, then the horse was a winner, and if the value is over 1, the horse lost.

I'm not sure where I am going with this at the moment, but if I can conditionally format a cell based on the value of another cell it will give me a starting point.

Hello,

Is it possible to add/make a newline on a VB button on a worksheet? How can
I do this? I added a button on a worksheet to run a macro. I need to make
the caption descriptive, but I don't want to drag the button to the length of
the activewindow. I can only add text through the property sheet of the
button. Is there a way to add a newline char to the caption text?

Thanks,
Rich

I have created a macro to select a row, copy it and then paste it below the
copied row. I want to attach this macro to a VB button labelled "Add Row" but
cannot work out how.

I have created a macro to select a row, copy it and then paste it below the
copied row. I want to attach this macro to a VB button labelled "Add Row" but
cannot work out how.

I am using Excell 2007 and am trying to set up a command button on a
worksheet to execute a VB routine. Here is what I am doing
While in a particular worksheet, I select the Developer tab and then select
Insert/Form Controls/Button

This creates a new button, in this instance with the name “Button 7”
Using the VCB Editor, I create, in a pre-existing Module,
Private Sub Button7_Click()
MsgBox "Button 7 pressed"
End Sub
Then when I go back into the sheet on which the button was created and
press the button, I do indeed get the message box “Botton 7 pressed” which is
encouraging.
When I right click on the button, I get some limited ability to edit the
button's properties, but I *cannot* change the button’s name. My questions
are

1) How might I get the ability to see the more of button’s
properties,including its name? Might I get the ability to change the
button’s name ? I have been playing around and eliminating buttons and now
when I create a new button it will be Button 8, but I have already
eliminated buttons 5, 6, & 7. Is there any way to get Excel to create
buttons with other names?

2) When I’m finished developing my app, I *don’t* want the user to be
able to inadvertantly right click on the button and be able to change its
properties, such as its location, size, text, etc. How might I prevent this?

--
-regards

Is is possible to automatically place a toggle button on a user form by entering data in a cell??

In otherwords, if I type a number in cell A1, can that number be automatically put on the toggle button and placed on active userform??

How do I show a user form as soon as my .xls file opens?
How to you put a control button on a worksheet and then assign macros to it?
Does anyone know if you can also do this in Word.

Thanks!!!!!!!!!!!!

Hello,

Is it possible to add an underscore to a visual basic button that is placed
on a worksheet from the visual basic toolbar? I tried adding & before a
given letter, but no underscore. How can this be done?

Thanks,
Rich

I am making an excel spreadsheet and I want to create a command button on the
sheet itself to where a user can go to another area, copy materal, come back
to the worksheet and click on the command button to paste information onto
the sheet to a specific cell without using keyboard shortcuts or the mouse..
Is this possible and how?

Does anyone know how to disable a custom button on excel 2007 ribbon?
I created a new tab in the menu wich contains two buttons but I want to disable the button untill a certain workbook is opened. right now the button are enabled even if there is no workbook opened, wich throws an error when they are clicked as they have a macro that do some calculations depending on data obtained from the file in question.
Thanks#
Giovetti

Hi experts,

Please help me, I need to know to how create a command button on one
worksheet, when clicked it spits out a static date on anther worksheet. For
example, I have an inspection sheet, when completed I click on the command
button and it spits out a date linking it to another worksheet. In that
worksheet there's a cell where i want the dates to pop up everytime i hit the
command button from another workbook.

Any tips will be much appreciated

How to create a toggle button on the menu? like the Bold, Underline...
button.

I am trying to create a complete button on a front sheet called Audit that will update a sheet called table by using a command button, that will automatically save the data on the table sheet but clear the Audit sheet ready for the next data entry. My data is entered into 13 cells in column C each cell with its separate title, and I am entering data in 5 separate cells in columns E and F. I just want to click a complete button so this data is entered in columns in the table sheet.

I have attached a copy of the sheet I am trying to use

Hey guys, back again.

A continuance of yesterdays problem, I'm looking to put a command button on a worksheet, or something similar.

Here's what happens:

- Worksheet loads, User form automatically loads and the user browses to the correct file to format.
- He clicks a command button which loads a macro which does the formatting and also highlights any incorrectly coded expenses, and then unloads the form.
- If any employee has incorrectly coded expenses he needs to check how they should have been coded and correct the data.

Now, I need to load another macro to input the data into ANOTHER worksheet and format it correctly.

The problem I'm having is that he is not the most computer literate person in the world, and I'd like to have a button, preferably a floating one on the worksheet so all he has to do is click it to run the 2nd macro and then it's done. Is there any way to do this?

Hi Everyone

I want to creat a 'save button' on this xls file using VBA, so once someone click on a ‘Save’ button that will ensure mandatory fields are input. (ie a pop-up will appear if some of the required field are not filled)
Could any experts help me with this (see attached file)

Many thanks

Paul

Hi Everyone

I want to create a "save button" on the xls file using VBA. After the members fill in the ‘CBP - Peer Review Feedback’ tab, they should click on a ‘Save’ button" that will ensure mandatory fields are input.

Very much appreciated if anyone could help me on this, thanks

The password for the xls file is pradmin

L

Hello All:

I am on a W2K computer with Excel 2000.

I am trying to hide a "Field Button" on a pivot chart when printing.

Is there a way of doing this?

Thanks,

Deguza

I wanted to create a control button (correct terminology?) on a
worksheet to execute a macro. I stubbled across two different types
of control buttons: one created by the Forms toolbar; the other
created by the Control Toolbox toolbar, which I understand is an
ActiveX control.

Since my macro was already written, the Forms control button seems
more straight-forward to associate a pre-existing macro with a control
button.

But I actually stumbled across the ActiveX control button first. That
required that I "rewrite" (cut-and-paste) my macro into an Excel
Object Sheet1 window instead of the Module1 window.

(Okay, I could have called the pre-existing macro from the "click"
macro. More about that below.)

This got me to wonder.... What are the pros and cons of each
approach? Why are there two appoaches?

I suspect the answer to the latter question is: I am not using one or
the other feature as it is intended to be used. Please elaborate.

Some things that I observed. Your comments would be appreciated....

First, as I said, the Forms control button set-up seemed more straight-
forward.

However, when I click on the ActiveX control button, I get visual
feedback. I don't get any visual feedback when I click on the Forms
control button.

Is there something that I need to do in order to get visual feedback
from the Forms control button? Or is that just the way the Forms
control button works?

(WAG: Perhaps because it normally brings up a user form, which would
serve as sufficient visual feedback.)

Second, I encountered a name conflict error when I tried to call the
pre-existing macro from the ActiveX "click" macro created in the Excel
Object window. That was actually due to my mistake, and I now know
how to avoid it. But I'm curious: what was the name conflict?

Here is what happened. After I created the Active control button
(with default name CommandButton1), I right-clicked on the button,
clicked Properties, and changed Name (as well as Caption) to Foobar,
the name of the Module1 macro. When I clicked on View Code, that
created the Excel Object Sheet1 macro to Foobar_Click(). In the VBE,
I entered the statement "call Foobar". When I tried to execute the
macro (either using F5 or exiting control design mode and clicking the
button), I got the error "expect procedure, not variable" on the Call
statement.

Of course, I solved the problem. (The obvious solution is to change
only Caption, not Name. But before I realized that, I simply renamed
the Module1 macro.) But I'm curious: why did I get the error in the
first place?

Apparently, there is a variable with the same name as the ActiveX
control button. But I don't see it. How can I see it? What is its
purpose?

Finally, what is the "preferred" approach to do what I wanted and why,
namely: to have a control button on the worksheet to execute a macro
(pre-existing or not)?

Well, most of the coding for the new quote module user form has been
going well. I have however, hit a snag. In the code below there is a
section that is causing an error. The debugger is saying the varible is
not defined. However, where it is stopping is not exactly a varible. It
is the name of a command button placed on the sheet mentioned in the
same line. Other than having the code select the right sheet first
(already incorporated into the code) I don't know how else to tell VBA
that it is a command button on the sheet and not one on the UserForm.

Any help will be appreciated.

The offending code is as follows:

Code:
--------------------
'
'Saves the New Quote in the template and disables the command button
ActiveWorkbook.SaveAs ThisWorkbook.Name
ActiveWorkbook.Sheets("Start Here").Activate
cmdStartWizard.Enabled = False
cmdStartWizard.Caption = "Quote Wizard Disabled," & Chr(13) & " please use editing button."
'
--------------------

And this is it in context of the entire click event:

Code:
--------------------
Private Sub Image4_Click()
'
'
'Selects the quote sheet and activates
ActiveWorkbook.Sheets("Final Quote").Activate
'
'Changes the Quote Number to Increase by 1
Range("J4").Value = Range("J4").Value + 1
Range("J4").Copy
Range("J4").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'
'Inserts the Deliverable Types into Cell J5
'

'
'Inserts the TAT and type into Cell M4, and N4 respectivly
Sheets("Final Quote").Range("M4").Value = txtTAT.Text
Sheets("Final Quote").Range("N4").Value = lbxTAType.Text
'
'Inserts the Project Reference into Cell J7
Sheets("Final Quote").Range("J7").Value = txtProjectReference
'
'Inserts the Company Name into Cell C12
Sheets("Final Quote").Range("C12").Value = cmbClientList
'
'Saves the file with a unique name
'Declares Variables of the file name for saved as function
Dim QNum As String
Dim CNam As String
Dim CrDt As String
Dim VNum As String
'
'Defines the variable names from Quote Form
CNam = Range("C12").Text
CrDt = Format(Now, "mmddyy")
VNum = Range("K4").Text
'
'Saves the New Quote in the template and disables the command button
ActiveWorkbook.SaveAs ThisWorkbook.Name
ActiveWorkbook.Sheets("Start Here").Activate
cmdStartWizard.Enabled = False
cmdStartWizard.Caption = "Quote Wizard Disabled," & Chr(13) & " please use editing button."
'
'Defines the QNum varible to the new quote number after the template is saved to insure they_
'are saved with the same number
ActiveWorkbook.Sheets("Final Quote").Activate
QNum = Range("J4").Text
'
'Saves the New Quote as a unique file
ActiveWorkbook.SaveAs _
"X:_FEE SCHEDULE & QUOTE MODULECreated Quotes" & QNum & VNum & "-" & CNam _
& "-" & CrDt
'
'Disables step 1 and Shows step 2
End Sub

--------------------

Here's to hope,
Amber

--
Amber_D_Laws
------------------------------------------------------------------------
Amber_D_Laws's Profile: http://www.excelforum.com/member.php...o&userid=30012
View this thread: http://www.excelforum.com/showthread...hreadid=507754

Hi All,

Could someone show me how to get VBA to 'Click' a button on a form pls.

I have a macro which does some stuff and then Shows my form. I then need it to immediately click a particular button on that form (This button does more stuff and then closes the form).

At the moment my form opens and users has to manually click.

Any advice is greatly appreciated.

thanks
John

Well, most of the coding for the new quote module user form has been going well. I have however, hit a snag. In the code below there is a section that is causing an error. The debugger is saying the varible is not defined. However, where it is stopping is not exactly a varible. It is the name of a command button placed on the sheet mentioned in the same line. Other than having the code select the right sheet first (already incorporated into the code) I don't know how else to tell VBA that it is a command button on the sheet and not one on the UserForm.

Any help will be appreciated.

The offending code is as follows:

'
'Saves the New Quote in the template and disables the command button
    ActiveWorkbook.SaveAs ThisWorkbook.Name
    ActiveWorkbook.Sheets("Start Here").Activate
    cmdStartWizard.Enabled = False
    cmdStartWizard.Caption = "Quote Wizard Disabled," & Chr(13) & " please use editing button."
'
And this is it in context of the entire click event:

Private Sub Image4_Click()
'
'
'Selects the quote sheet and activates
    ActiveWorkbook.Sheets("Final Quote").Activate
'
'Changes the Quote Number to Increase by 1
    Range("J4").Value = Range("J4").Value + 1
    Range("J4").Copy
    Range("J4").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
'
'Inserts the Deliverable Types into Cell J5
'

'
'Inserts the TAT and type into Cell M4, and N4 respectivly
    Sheets("Final Quote").Range("M4").Value = txtTAT.Text
    Sheets("Final Quote").Range("N4").Value = lbxTAType.Text
'
'Inserts the Project Reference into Cell J7
    Sheets("Final Quote").Range("J7").Value = txtProjectReference
'
'Inserts the Company Name into Cell C12
    Sheets("Final Quote").Range("C12").Value = cmbClientList
'
'Saves the file with a unique name
'Declares Variables of the file name for saved as function
    Dim QNum As String
    Dim CNam As String
    Dim CrDt As String
    Dim VNum As String
'
'Defines the variable names from Quote Form
    CNam = Range("C12").Text
    CrDt = Format(Now, "mmddyy")
    VNum = Range("K4").Text
'
'Saves the New Quote in the template and disables the command button
    ActiveWorkbook.SaveAs ThisWorkbook.Name
    ActiveWorkbook.Sheets("Start Here").Activate
    cmdStartWizard.Enabled = False
    cmdStartWizard.Caption = "Quote Wizard Disabled," & Chr(13) & " please use editing button."
'
'Defines the QNum varible to the new quote number after the template is saved to insure they_
'are saved with the same number
    ActiveWorkbook.Sheets("Final Quote").Activate
    QNum = Range("J4").Text
'
'Saves the New Quote as a unique file
    ActiveWorkbook.SaveAs _
    "X:_FEE SCHEDULE & QUOTE MODULECreated Quotes" & QNum & VNum & "-" & CNam _
    & "-" & CrDt
'
'Disables step 1 and Shows step 2
End Sub
Here's to hope,
Amber

Is it possible to change print ink color based on value in a cell for a
spreadsheet application? Example: If a cell's value is over 250, can I have
it print the number in red ink to draw my attention to it??

Hello All:

I am on a W2K computer with Excel 2000.

I am trying to hide a "Field Button" on a pivot chart when printing.

Is there a way of doing this?

Thanks,

Deguza

Hi Guys

Any suggestions on how to format a single cell to look like a 3D button. I need to place several of these in adjacent cells. I have excel 2000. I believe that 2010 has this facility but I dont have access to it!

Thanks