Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Cell Reference From Clicked Button

There was a similar question I found answered in the archives. However the solution given there (using ActiveSheet.Buttons(Application.Caller) does not work in my case.

A little googling and I think the reason is that I added my buttons from the toolbar and not from the forms editor. My buttons are in the OLEObjects collection, not in Buttons.

So how do I determine which of my OLEObjects was clicked? Is there something similar to "Application.Caller"?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Reference Cell Based On Button Location & Determine Button Used
I received this code from an example I found once upon a time that was originally submitted by someone else. Right now the code enters the current date in a cell of the same row as the checkbox when it's checked. I need to revise it, or come up with something similar, that will reference the value of a cell when it is in the same row as the button (from the Forms toolbar) that is clicked to activate the macro. First of all, I don't know how to reference a Forms button in VBA.

Sub Process_CheckBox()
Dim cBox As CheckBox
Dim LRow As Integer
Dim LRange As String
LName = Application.Caller
Set cBox = ActiveSheet.CheckBoxes(LName)
' Find row that checkbox resides in
LRow = cBox.TopLeftCell.Row
LRange = "B" & CStr(LRow)
'Change date in column B, if checkbox is checked
If cBox.Value > 0 Then
ActiveSheet.Range(LRange).Value = Date
'Clear date in column B, if checkbox is unchecked
ActiveSheet.Range(LRange).Value = Null
End If
End Sub

Here is some other code I already created. Unfortunately, because I don't know how to do the row reference, I had to create 25 different macros, which just bulks up the size of my file and slows it down. But here is what I'm trying to accomplish in my macro:.............

View Replies!   View Related
Determining Button Clicked
I have a spreadsheet with a variable number of xlButtonControl type form controls. The actual number of buttons depends on the amount of data that the spreadsheet is being asked to summarise. All the button controls use the ".OnAction" property to point execution on click to a common procedure.

Unfortunately that much is what it is and is beyond my control, so I just have to work with that.

What I would like to know is if there is a property or method I can access from within the common OnAction procedure that enable me to identify which button was pressed. If this was a CommandBar environment I would use the ActionControl property. Since it's not, and since I can't change it (not even to use ActiveX controls!), does anyone know an equivalent that I can use in this case?

View Replies!   View Related
Call Macro If Button Is Clicked
I need a code for "click-able button" in my excel sheet that will call makro "copy" which I have connected with ThisWorkBook /Sub copy() .../ or in other case with Modules (module 7 for example).

So, what to add between this:

Private Sub CommandButton1_Click()


End Sub

View Replies!   View Related
Get Column Number Of Clicked Button
Sub Mainscoresheet()
Dim k As Integer, i As Integer, cs As Integer
Set b = ActiveSheet.Buttons(Application.Caller)
With b.TopLeftCell
cs = .Column
End With

I have a button assigned to this macro about 20 times all over my worksheet
I will place the button in dirrerent locations. What I need to do is get the column that the top-left corner of the button and assign that to "cs".

Example if the top left corner of the button is in column "F" then cs would be 6.

View Replies!   View Related
Stop Loop When Button Clicked
I have a form with two buttons, one is to start downloading, one is to stop it, a sub is called when start buuton is clicked, there is a DO LOOP, I hope the DO LOOP stop when I clicked STOP button,

View Replies!   View Related
Disable Toolbar Button Until Another Clicked
I have two custom toolbar buttons. I want to disable one of them until the second will be pressed (sorry for my english).

View Replies!   View Related
1 Command Button To Do Different Task When Clicked A 2nd Time
Currently I have 5 identical command buttons which do something similar but in a different cell.

What i want to do is sum up the 5 different task using a single command button.

When cmdaddsp1 is clicked the first time cell "B4" is populated with a value from a table.
When cmdaddsp1 is clicked a second time cell "B5" is to be populated with a value from a table.
When cmdaddsp1 is clicked a third time cell "B6" is to be populated from the table,
and so forth.
Is there a loop or anything that can be used for this? I only want other cells to be populated when the command button is pressed.

View Replies!   View Related
Button When Clicked Copy Values From A Column
i want to create a button in excel worksheet that when clicked copy values from a column (say A1 to A25) and store it so that it can be copied to word file or notepad or even in other excel worksheet. so i think i need a macro for this or it can be done by writing VBA Code.

View Replies!   View Related
Reset A Textbox In Userform When Button Is Clicked -
I can't find a way to reset/clear the textbox in order to enter new data

View Replies!   View Related
Form To Enter Date When Button Clicked
I am looking to create a form that when a user enters data in it, and clicks a save button it will submit that data into cell A2, and then with the next time the enter data it will submit that data to cell A3. I would like it to continue moving down a cell each time somone hits a "submit" button.

View Replies!   View Related
Radio Button Transparency Lost When Clicked, How To Remedy
I have a form (worksheet with controls embedded in) and the form changes color depending on what the user clicked, problem is that button colors and cell pattern colors don't quite match.

thought I solved it by making the buttons backstyle = transparent.
However, as soon as radio button was clicked, transparency was lost.

I have named the range of the form "FormGround" so that when Service 1 is select, the Form turn a different colour. I have my subsequent buttons change to that same color as well (using the cell's color index number.

View Replies!   View Related
Spin Button To Increase Or Decrease By 1 Each Time It Is Clicked Up Or Down
I am having difficulty finding information on coding my spin button on a user form. I searched and haven't found any information. I need to do is code a spin button to increase or decrease by 1 each time it is clicked up or down. I would like it to populate to a text box on my form if that is possible.

View Replies!   View Related
Triggered When The Visaul Basic Editor Command Button Is Clicked
Can a event be triggered when the Visaul Basic Editor command button is clicked in Excel?

View Replies!   View Related
When Cell Clicked, Stays "clicked" Highlights All
What in the world did I do now? When I click on any cell, the cursor turns into a thick "+" . Then, when I move my mouse up and down the spreadsheet, all the cells the "+" touches become highlighted.

View Replies!   View Related
When A Cell Is Clicked The Row Also Highlights - But How
I've seen a spreadsheet where the user can click on a cell, and the row the cell is in automatically highlights.

This is useful where there is a lot of columns, and it also looks pretty snazzy!

How do I do this? I know one way is to put the following code in when you right click the sheet tab and choose "view code":

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
UsedRange.EntireRow.Interior.ColorIndex = xlNone
ActiveCell.EntireRow.Interior.ColorIndex = 6
End Sub

But then I can't have nice background colours for other cells. Any suggestions?

btw, I've v. new to VBA - best to assume I know nothing!

View Replies!   View Related
Add Clicked Cell Value To Formula
The idea is that once macro is started it will add the value of any clicked cell into the formula of the original cell.
1.start macro cell a5 (value=36)
3.type '+' cell b7 (value=21) enter to end macro
Result will be '=36+21' in the formula bar. The cell will then show the answer 57.

View Replies!   View Related
Insert A Value Into The Cell When Clicked In Vba
I need some VBA code which will insert a value into the cell when clicked.

First click = Value: 1
Second Click = Value: 2
Third Click = Value: "" (BLANK)

Cycle back to first click.

View Replies!   View Related
Pop-up Calendar When Cell Is Clicked
Is it possible to modify the following codes so that if the active cell is formatted as Date when the cell is clicked the pop-up calendar shows.

View Replies!   View Related
Change Cell Colour When Clicked
I've found a great thread that answered part of my problem (Change colour of text on update of cell)

This code changes the colour of a cell when selected. With the application I'm trying to make I need this effect to only occur in a certain area say A10:D30

I'm trying to make a scheduling page that uses timebars to represent when people are working. I though if a user could click or highlight the hours and a time bar produced I could lookup to see what hour the timebar starts and ends and show the hours worked. I also need it to cycle through 5 different colours before returning to white.

Private Sub worksheet_change(ByVal target As Range)

target.Interior.ColorIndex = 4

End Sub

View Replies!   View Related
Change Active Cell After Hyperlink Clicked
I have a column with entrys of 2 kinds. Some being Hyperlinks and others with normal type data. All cells are locked with password except for cells that will possibly have entrys made in them.

Is there a way to move the active cell to R1C1 after any hyperlink clicked? R1C1 is unlocked.

View Replies!   View Related
Override The Data In The Cell When Checkbox Is Clicked
i am working on a spreadsheet that is doing two things 1) its allowing the user to choose between 1 to 3 number or N/A from the list validation option and 2) there is a checkbox if one selects that all the columns that have the formula will become N/A irrespect of data in it. I am using the following formula in the list validation option a3 is the check boc.

E5 =1
E6= 2
E7 = 3

=if(a3=true, $E$8, $E$5:$E$8)

The issue I am having with this is, if I start selecting something from the drop down and half way I realize I need to check the box, it will only make it E8 value for the cells I have not touched and the ones I have already selected the drop down value it will remain. However I want it to override the value to E8 irrespect.

1) If the user of the spreadsheet clicks on the check box (this is in A3 cell) which i have linked it using format control then all the cell (Column E) that have the formula =if(A3=True, "N/A", ) will have N/A which is not applicable and not an error as you mentioned. I have successfully implemented this

2) If the user does not select the check box (a3) then each of the cell (Column E) has a drop down which the user can select either 1,2,3 or N/A. Which I have successfully implemented by using the data validations. Now the struggle I am having is as follows: I have attached the spreadsheet for your reference too.

Now if I select number 2 in cell E10, 2 in cell E11 and 3 in cell E 13 and you can see alll the other cell in column E are 0, because of the formula and the box in A3 is not checked in Tab 1. In Tab 2 I have checked the box everything turned N/A in column E except cells E10, E11 and E13 because I had previously selected 2,2,3 respectively. This is not what I want, what I want is when I check that box everything should turn to N/A basically an override function is what I want to created.

View Replies!   View Related
Log Number Of Times A Hyperlink Cell Is Clicked
I have a spreadsheet with contact information in it. I am trying to figure out a way to count how many times I click a contacts email address, then tally it in a weekly log. The end result will be a page that totals how many emails I sent each week for the year based on the number of clicks I have made to the spreadsheet.
Ideally I would also like to log the number of times the phone number is clicked too, but I am unsure how to make the phone number a live cell.

View Replies!   View Related
Without Closing A Form How Can I Show Values Of Clicked Cell?
I attached an excel that I created. But I have one problem with it.

View Replies!   View Related
Activecell Reference From Command Button
I have about 200 command buttons on my spreadsheet. Since there are so many of these, I would like to create a VBA routine to determine the cell name four cells over from the cell each command button sits inside, if possible (each command button is small and sits within its own cell). So the command button in cell F1, will let me pull the data or get the cell name of cell C2.

So I guess I am trying to figure out how to do relative references from a command button. Then this will be put in a public variable and passed to a subroutine which is the same for each command button. Only the data in the cells relative to the command button changes. I thought activecell might be useful for this, but you when press a command button, it doesn't make that cell the active cell.

I just thought there might be a simple solution for this, instead of having each single command button have its own subroutine just so each one can reference cells. All I really need is to run the same subroutine with variables from 200 cells, one at a time.

View Replies!   View Related
Relative Reference Button Disappeared
When I click Tools Macros Record New Macro I usually get a box which allows me to chose relative reference and is displayed until I click stop.

View Replies!   View Related
Reference Worksheet Command Button
I need to enable and disable command buttons in my code. These command buttons are located on sheets. I assume that I can simply set the Enable property, but I am having trouble referencing the command button. I thought I could simply reference it by name but that is not working.

View Replies!   View Related
Error On Button Code Reference To A Subroutine
I'm having trouble calling a subroutine from a command button. It's puzzling because I've set up buttons before and didn't have this trouble.

Here is my button
Private Sub EPConversionButton1_Click(ByVal target As Range)
Convert_Hrs_EP target
End Sub
And here is the subroutine.

Sub Convert_Hrs_EP(target As Range)
End Sub
There's nothing there yet, but I keep getting error messages regarding the transfer from the button code to the subroutine.

The message is: Procedure declaration does not match description of event or procedure having the same name. The Help file says this means that my procedure has the same name as an event, but does not have the same signature. But it's not so.

View Replies!   View Related
Copy/paste-button - Wrong Reference Code In Sheet 2
i have a button that copies data from cell
A5:K5, and pastes it in Sheet2 of my workbook.

The data is ordered like this

------ button1

------ button2

------ buttonX

I have like 40 buttons attached to 4 rows each. And in that group of rows, i have some cells with an IF= formula, and some minor coding.
The problem is: If i press button 3,4,17,29 before i press every other button before that. I get an reference code fail at the cell.

If i press the buttons in order. Like 1,2,3,4,5,6,7 - it appears smoothly.

More info: The button copies data from for example A5:K5 and pastes it into the next blank row of sheet2. But i want to be able to press button 2 before i press button1, without getting an error in my =IF cell.

If i press the 40 buttons in a row, and then starts to mix up the button pressing. It appears smoothly.

View Replies!   View Related
Cell Reference :: Formula To Reference New Cells
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.


12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.

View Replies!   View Related
Absolute Reference (cell Reference Behind The Table)
I have a table that displays data from another worksheet. This is what the cell reference behind the table look like:

View Replies!   View Related
Cell Reference- Able To Reference Two Cells To The Left Even As More Cells Are Added
I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C

As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.

I also need E (which has also shifted over one) to sum A,B,C, and C2.

Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.

View Replies!   View Related
Circular Reference: Cell References In The Formula Refer To The Formula's Result, Creating A Circular Reference
I have the following formula in cell L51 of all sheets calculating the volume depending on the monthly index that is chosen from the drop down menu in a particular sheet. =If(MIndex=0, SUM(D33:L50),If(MIndex=1,SUM(D34:L50),If(MIndex=2,SUM(D35:L50), 0))). I am getting the following message and I do not understand what it is about.

Microsoft Office Excel cannot calculate a formula. Cell references in the formula refer to the formula's result, creating a circular reference. Try one of the following

View Replies!   View Related
Get The Name Of The Shape Clicked
Is there any way that I can get the name of the shape i just clicked to call a procedure?

I have 35 shapes in my Worksheet.
Each shape has a different name. Each shape has a macro procedure assigned, wich do almost the same procedure (with just small differences, depending on from wich shape the macro was called).

I want to make just one macro procedure and catch from wich shape the macro was called.

View Replies!   View Related
Which Hyperlink Was Clicked And Associated Shape Name
How do I capture which hyperlink has been clicked (selected in any fashion)?

Then get associated shape name.

View Replies!   View Related
OptionButtons Keep On Growing When Clicked
I've been making a spreadsheet with lots of optionbuttons, and I'm slowly starting to understand how to link them to values, execute VBA script when their value changes, etc.

There is a problem that completely puzzles me. When I click the button (not in Design Mode), or any of the other buttons in the same group, they keep on growing! Please see the attached picture for an idea of what it looks like after a few clicks. I want the text and button size to be stable. I haven't changed the zoom, column sizes, etc. so I can't be that. They grow by simply clicking buttons in the same group.

View Replies!   View Related
Remove The (x) Or What Value Is Returned When The (x) Is Clicked
I have a user form where a user chooses a file. The form works fine, but if the user clicks the (x) in the top right of the box I get an error. how to either remove the (x) or what value is returned when the (x) is clicked so that I can put some handeling for it in my script.

View Replies!   View Related
Detecting What Object Was Clicked
I've been working on a visualizer for large data sets and I'm trying to create a flexible interface. What I am trying to do is populate a sheet with objects(rectangles, triangles, etc.) and linking them all to the same macro. The macro would detect what had clicked it and read the alternative text which would hold the command procedure for that object. I've seen how people detect cursor position but I have not come across anything to detect the object that was clicked. The code I've found (in another post) is as follows:

Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
x As Long
y As Long
End Type

Sub CurosrXY_Pixels()

Dim lngStatus As Long
Dim typWhere As POINTAPI

lngStatus = GetCursorPos(typWhere)
MsgBox "x: " & typWhere.x & Chr(13) & "y: " & typWhere.y, vbInformation, "Pixels"
End Sub

This works but I can't figure out how to detect the object it clicked. I was hoping to use

ClickedObject = ActiveWindow.rangefrompoint(typeWhere.x, typeWhere.y)
Command = ClickedObject.AlternativeText

but all I get is errors.

View Replies!   View Related
Open The Hidden Worksheet When Clicked
1) I cannot figure out how to write VBA so the "Show Results" button will open the hidden worksheet when clicked.

2) When I use the userform to add new info, it does save to the worksheet, but when I cose the userform and try to open the spreadsheet, I get the following message, "Userform is already opened. Reopening will cause any changes to be disguarded". If I answer yes, I lose my changes, if I answer no, the changes are saved. I want this message to go away and data from the userform to automatically be added to the spreadsheet.

3) I want to make sure the "Account Number" field has a 9 digit number in it before it can be saved. If there is not a 9 digit number I would like an error message saying "Please enter a 9 digit account number" as a pop up.

4) I want to make sure both the "Account Number" and the "Assignment" fields have been populated before the data can be saved.

View Replies!   View Related
Color UserForm CommandButton When Clicked
is there ant way to clearly highlight command buttons on a userform that the program user selected (clicked on) ie change its clour or raise it etc so the user can easly see which button they have clicked on.

View Replies!   View Related
Filling A Textbox After A Listbox Is Clicked
I'm having some trouble with the following. On a userform, I have 3 listboxes:


When I change the value of any one of the Listboxes, I would like to fill the corresponding TextBox. They're named:


So, if I:

* change ListBox1Category, I want to have 20 (for example) in TextBox1Amount
* change ListBox2Category, I want to have 20 (for example) in TextBox2Amount
* change ListBox3Category, I want to have 20 (for example) in TextBox3Amount

So the numbers 1, 2, 3 should match. Does anyone know the code for this?

View Replies!   View Related
Control Toolbox Not Appearing When Clicked
i have been having this problem with my version of excel at work and it is driving me crazy. First of all this is excel 2000. For a while now I have been unable to use the control toolbox - when I click it - nothing happens. In the meantime I havent been doing anthing really serious so I used the forms toolbar. I have tried using the control toolbox in word - and it works - just in case someone had been messing around and uninstalling things. Is there anyway that I have hidden it somewhere?

View Replies!   View Related
Look At Data From One Cell Reference Another Cell Then Display The Results From The Cell Next To It
I have 2 problems relating to LOOKUP.
Not sure if Excel can perform these calculations as they could get to complex.

Problem 1
Can it be possible to have excel look at data from one cell reference another cell then display the results from the cell next to it in another cell, sort of example:

Tab 1 (Never changes)

Tab 2 (Dynamic, changes each week)

So it would work as follows.
Tab2 column B will take Tab2 column As data check Tab1 column A and display Tab1 column Bs result.

Problem 2

Weekly league rank table that shows position movements week by week


1FredUp 2
2BobNot Moved
3JonDown 2

Can Excel calculate/show the actual movements of league positions?

View Replies!   View Related
Opens But Doesn't Load The File I Just Clicked On?
if i double click to open a xls file (excel not open) then excel opens but doesn't load the file i just clicked on?

View Replies!   View Related
Have Postcodes Flag Up When Entered Or Start Clicked
I have a spreadsheet with many many worksheets & on each of those worksheets many many postcodes.

I am looking for a way where I can have a list of postcode stored once somewhere (in excel, word or whatever) & then when we type postcodes into the Excel spreadsheet & click whatever to start the macro or run the code it will refer to where I have the postcodes saved & then highlight any that match on the worksheet page.

View Replies!   View Related
Make Certain Worksheets Show/hide When Others Are Clicked
Essentially what I want can be shown in outline form:

I. Stats (workbook)
A. 2009 (worksheet)
1. Jan (worksheet that appears only when 2009 worksheet is clicked)
2. Feb (worksheet that appears only when 2009 worksheet is clicked)
B. 2010 (worksheet)
1. Jan (worksheet that appears only when 2010 worksheet is clicked)
2. Feb (worksheet that appears only when 2010 worksheet is clicked)

I was hoping there was a way to have one workbook that displays only the years (2009, 2010) as worksheet tabs but once clicked, would reveal 12 nested worksheets (one for each month). Once a different year worksheet was clicked, the month worksheets currently displayed would hide again.

View Replies!   View Related
Calender Enters Date Into Clicked On Textbox
I have a userform (FrmComp) and in it i have several Textboxes. When i click on any of the textboxes the calender appears but how i i make the calender assign the date value selected on it to the last clicked on textbox? here's what i have:

View Replies!   View Related
Determine Shape Clicked For Running Macro
I have map shape in my excel sheet. each country is a shapes on its own. If i Select one of the country i want to display a pop up Box. For examle if i select Americas I need it to display another box with some data and when i deselect it it should hide the box. Tried some code over here but does not work.

Sub HideShowRectangle()
With ActiveSheet.Shapes("Data1")
If ActiveSheet.Shapes("Americas").Select = "True" Then
.Visible = True
.Visible = False
End If
End With
End Sub

View Replies!   View Related
Option To Hide The Sheet When Disable Macros Is Clicked
Is there any option to deactivate the "Disable macros"


When i open the userform it asks for enable and diable macros. If i click "Disable macros" then the users can manipulate the data inside the excel sheet. i need to avoid this problem.

So, is there any option that if i do "Disable macro" then the corresponding sheet for the which the Userform is linked should not be visible or hidden.

View Replies!   View Related
Automatically Copy And Paste Certain Data To Another Worksheet When Clicked
I have a command button that I want to automatically copy and paste certain data to another worksheet when clicked. I keep getting a "runtime error 1004; application-defined or object-defined error". The code I have so far is:

View Replies!   View Related
Hyperlink To Closed Files Display Error When Clicked
I am automatically generating a hyperlink to another document. The link that generates is: (I don't know if this qualifies as code, but I'll tag it anyhow...)

06-09-05 0000020 Name/06-09-05 0000020 Name.xls# 'Notes'A5

with 06-09-05 0000020 Name as a directory off the current App.path, and 06-09-05 0000020 Name.xls being the name of the file in the directory. It does have a sheet called "Notes". The thing of it is, the hyperlink takes me to the right place, it just gives me an error message every time saying "Reference not valid". How can it be taking me to the right place, and still giving me the error?

View Replies!   View Related
Copyright 2005-08, All rights reserved