Activecell Reference From Command Button
May 6, 2009
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 3 Replies
ADVERTISEMENT
Mar 5, 2009
I have a spreadsheet that automatically analyzes the last week's worth of data and then displays a table of summary data from that analysis in the range C2:G11. Each row (i.e. 2:11) represents the statistics from one of the 10 regions in the state. I then have a second "details" table which remains blank (it occupies the range C14:G20; one row for each day of the week) until an "x" is typed in Column H in the cell adjacent to a region's summary data above, e.g.
if I want to display detailed data for region 4 for the last week, I would type an "x" in cell H5; formulas in the blank "details" table then lookup information on region 4 from a larger dataset. This system works fine, but is a little clumsy (typing "x"'s and deleting previous "x"'s, etc.).
Here's what I would like:
1): A macro for this sheet that will place an "x" in the ActiveCell (only in the case that the active cell is somewhere in the range H2:H11), will remove that "x" when that cell is no longer active, and will not place more than one "x" at a time-- for instance if more than one cell is accidentally highlighted
2): Also, I would like to be able to paste the values and formatting of the "details" table (C14:G20) to a separate sheet (we'll call it Sheet2) if they're interesting, and I'd like to be able to do this with a command button. The problem is I don't know... 1: how to place a button on the sheet, 2: how to attach code to this button, 3: where to place this code (can it go in the same place as the ActiveCell macro I described earlier?).
Ideally, the code would allow me to paste an arbitrary number of these tables to Sheet2, so the code would need to be able to identify the first blank cell in Column A as the location to paste a new table when the button is clicked.
View 2 Replies
View Related
Jan 5, 2008
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 3 Replies
View Related
Apr 14, 2009
Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).
View 9 Replies
View Related
Nov 4, 2008
I've been trying to get the sum of a range of cells based on their relation to the active cell. Here is the Pseudo
If the SUM of (the cell 2 rows up THROUGH the cell 2 rows up and 2 columns to the left) = 0 then .......
*****end pseudo
I think that activecell.offset is the best way to do it, but I can't figure out how to work that in a range....
Here is my code so far:
Worksheets("Generic SPC").Activate
Worksheets("Generic SPC").Range("c2").Select
'Do for all cells in the row
Do
ActiveCell.Offset(0, 1).Select
If Application.WorksheetFunction.Sum(Range( _
ActiveCell.Offset(-2, 0), ActiveCell.Offset(-2, 2))) = 0 Then
ActiveCell.Value = 0
ElseIf Application.WorksheetFunction.Sum(Range _
("ActiveCell.Offset(-2, 1), ActiveCell.Offset(-2, 3)")) = 0 Then
ActiveCell.Value = ActiveCell.Offset(-2, 0).Value
Else: ActiveCell.Value = 0
End If
Loop Until IsEmpty(ActiveCell.Offset(0, 1)) = True
******End Code
Range doesn't like activecell.offset. Does anyone know how to do this?
View 9 Replies
View Related
Feb 3, 2010
How can I return name of a command button on click? I want to create some sort buttons on a sheet I will use regularly to speed things up. I thought if I could call each sort button by the column letter the button sits in I can use this in a single sub for all buttons
e.g the button sitting in column A is called "A". when i click the button, it returns it's name to a variable which I can then use to sort column A. I know I could create a seperate routine for each button but I was just trying to think of something neater.
View 2 Replies
View Related
Aug 31, 2007
I have a userform with a button on it named But1. Is it possible to store But1 into a variable then activate the But1_Click action using a variable?
Dim strButname As String
strButname ="But1"
Now, how would you activate the But1_Click method using strButname variable. With listboxes you can use the Control(strListboxName) methodology?
This doesn't work, but gives you an idea of what I am trying to do.
Controls(strButname)_click
View 9 Replies
View Related
Nov 8, 2012
I currently am using a form button to create new rows.
I need two different kind of buttons. I have a button that does this-
VB:
Sub InsertRow()
'this inserts a row where the button is clicked.
Dim r As Range
[Code]....
However, when I try and create these codes using a Command button (I need it for the color), I get the error
"unable to get the buttons property of the Worksheet class"
P.S one other thing, say the button is in row 3, and Cells A1 and A2 are merged, when you click the button in row 3, it should extend the merge to A3.
View 4 Replies
View Related
Mar 9, 2013
i want to create a save button in sheet 1, on clicking the same the data entered in the particluar cells of sheet 1 should get saved in sheet 2 in given format
View 1 Replies
View Related
Oct 5, 2006
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"
ActiveSheet.Select
Range("B4:B37").Select
Selection.ClearContents
Range("G4:G37").Select
Selection.ClearContents
Range("B1").Select
Selection.ClearContents
Range("D1").Select
Selection.ClearContents
Range("F1").Select
Selection.ClearContents
Range("J1").Select
Selection.ClearContents
Range("M2:M3").Select
Selection.ClearContents
Range("B4").Select
ActiveWorkbook.Save
Application.Quit
View 2 Replies
View Related
Mar 14, 2007
I have a sheet were i want to insert a new blank line at a spcecific point.
When a cell, ex. A28 is marked, i have made a macro that makes a new line at that point. The only thing is, i cant figure out how i make alterations to a certain cell in that row (to make sure it is the same every time.. there will be many users to the document, and i dont want any to make some big stupid mistakes by altering something, so every new row should be formatted the same way every time..)
I have recorded the following:
Sub ActiveRowTest()
'
Rows("28:28").Select
Selection.Insert Shift:=xlDown
Range("K28").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("H28,F28,E28,K28").Select
Selection.NumberFormat = "#,##0".................
View 9 Replies
View Related
Aug 13, 2008
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.
View 8 Replies
View Related
Apr 6, 2007
Does this code copy all cells from the active cell up to the last non-blank cell, or is it up to the first blank cell after the last non-blank cell?
View 9 Replies
View Related
Mar 7, 2008
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
Else
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 8 Replies
View Related
Jul 29, 2008
I have searched this topic but I came up with nothing regarding my exact situation.
I have 5 buttons on sheet 1. I want them to all direct me to another sheet in the same workbook. However, some buttons will contain more than one sheet.
Example: Button 1 will direct me to 4 different sheets. Is this possible?
Also, When I open the workbook, I see many sheets that I am working from but I do not want them to show up when I open the workbook. The only way to see these sheets is to click on the command buttons at the top of sheet 1.
View 10 Replies
View Related
Aug 20, 2009
When I press a command button I want to:
Uncheck a checkbox made with Control Toolbox
Check a particular Option Button within a group box made with the Forms Toolbar.
EDIT: Clarification, I want both things to happen when I click one button.
View 3 Replies
View Related
Jul 6, 2007
Is it possible to check multiple checkboxes with a command button?
For example, I have 20 checkboxes (C5 to C25) when command button is clicked
C5, C12, C17, C20 would be checked.
View 10 Replies
View Related
Jun 12, 2014
how to keep a command button in a certain location even if the a row or Column is Deleted or Inserted into the worksheet?
View 1 Replies
View Related
Jul 31, 2014
I know that this may seem a little strange to request as you can just select the row and delete it, however the sheet is designed as "idiot proof", so I would like to implement this.
Basically, I have a box which states "If you need to delete a row due to incorrect entry, please type the row number here (next cell), and click delete".
I want to know if by using VBA, you can reference the number entered in the cell, and use this to determin the row in which we are required to delete.
View 8 Replies
View Related
Apr 29, 2008
I have a 2 worksheets. One "Projects in Process"and the other "Project Completed". when the users fill in the information in cells A4-F4 (line 4 is the starting line and it could go down as far as line 23 or a23 - f23) In Projects in Process they press a "completed Project" command button and the data in a4-f4 copies over to worksheet "Projects completed". In the next available row (starting at row 4).
View 13 Replies
View Related
Aug 20, 2008
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.
View 9 Replies
View Related
Sep 25, 2008
I want to create a command button that will clear whatever is written in a text box. I guess for this to work it needs to select all, then clear. The command button and the text box have been created using the control toolbox.
View 2 Replies
View Related
Dec 13, 2008
I have created a userform with text boxes and a list box. I have a few questions. First for the list box. I got the information for my list box through the list box example sheet2!A1:A1000. Is there any easier way ? Next I would like to create a New Command button to create a new record. I decided to just do a clear all textboxes, option buttons ect.. to clear, is there an easier way ? I seem to be looping sort of way when I get to txtissue.Value = "" it than goes to txtIssue_Change()
View 14 Replies
View Related
Apr 9, 2009
I have attached a spreadsheet example. I would like to be able to use a command button to pop up a series of questions for the user to input a value and have that value populate the directed cell.
Example from spread sheet:Click - "How many bid" button
First question should be " How many for Joe"
Input the number i.e. 2
The number input should go to the cell C5 in this example
Next question should be "How many for John"
I want to input the number 3
The number 3 should go into cell F5
How many for Jim
I need to input a number
The number should go to cell I5
How Many for Jerry
I need to input a number
The number should go to cell L5
I would also like the same function for the "How many won" button.
View 4 Replies
View Related
May 26, 2009
I have few problems with the below script. First I dont know exactly where I want the button to be placed since the data changes from week to week. Is there a way for me to place the button with in some merged cells? If so How can I merge some cells when I dont know where the data will end. I'm using this to find the last empty cell in Column "B" plus 2 cells down.
View 2 Replies
View Related
Oct 30, 2009
I want to insert a command button and assign a macro, I have the developers toolbar available with the controls section and on all other spreadsheets i can then select insert and choose the required button, however on the particular workbook i want to use the insert button is greyed out and not available.
i already use macros in this workbook and they work fine from keyboard shortcuts but i would like to make one of them run from a command button
Is there a simple setting somewhere which i may have turned off inadvertently previously or could the problem have anything to do with the macros themselves?
View 10 Replies
View Related
Dec 20, 2009
I have created a command button from the Excel 2003 from the Macros toolbar that I placed on my worksheet. I want to use this button to activate an existing macro. Currently I have no code entered for this button and the button when clicked on behaves as an object to be edited, moved, etc. How should I proceed?
View 6 Replies
View Related
Jan 28, 2012
I have 2 Subs in Module 1
I want to link them to a button
When I create a "ActiveX Command Button" it is only links to the worksheet (say Sheet1)
I have tired changing the name below to the Sub in the module but doesn't work.
Private Sub CommandButton1_Click()
End Sub
View 3 Replies
View Related
Apr 26, 2007
If I insert a command button into an excel spreadsheet on 'Sheet1', how do I make it a hyperlink button that will take me to cell A1 on the 'Sheet2' tab?
View 9 Replies
View Related
Apr 13, 2008
The code below is supposed to disable the appropriate command buttons when it's associated cell values are 0.
Private Sub Worksheet_Activate()
If Range("C17").Value = 0 Then
ActiveSheet.CommandButton7.Enabled = False
ActiveSheet.CommandButton4.Enabled = False
Else
ActiveSheet.CommandButton7.Enabled = True
ActiveSheet.CommandButton4.Enabled = True
End If
Not working ..... no buttons are disabled even with their cells are zero. Did I forget something?
View 9 Replies
View Related