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
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Show/Hide Command Button Based On Location
What i am trying to do is make a command button appear when you hit any cell of the row its on. For example, If you were to hit any cell on rows 1 or 2 the command button will appear and when I am not on the visible property goes back to false. I have a ton of buttons on this sheet and I am trying to clean it up so buttons only appear as needed.
View Replies!
View Related
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).TopLeftCell.xxx) 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 Replies!
View Related
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.
View Replies!
View Related
Determine Chosen Option Button On Worksheet
I am having trouble writing a macro that checks the value of an option button to change a string accordingly. The ActiveMonth variable is used to copy information across from one work sheet to another, with the worksheet depending on which of the option buttons is selected. Static ActiveMonth As String
View Replies!
View Related
Determine Position Of Shape/Button On Worksheet
I have written a small piece of code that handles clicks on buttons on an excel spread sheet. When a button is pressed, i need to know the row number where the button resides in. I have searched and read about this issue on several forums, and they all indicate that i need to use something like application.caller.topleftcell.row to get the row number and application.caller.topleftcell.column to get the column number. However, when i use the row variant, it ALWAYS returns row number 1. When i use the column variant, it returns the column number it is actually in. i have added the full code that is creating the buttons below, and also the part that displayes the rownumber that is incorrect. Private Sub Workbook_Open() Dim name Dim time As Integer time = InputBox("Typ the hour you are checking" & vbCrLf & "eg: 7, 10, 13, 14, 15, 16, 17, 18, 19", "Which Check")
View Replies!
View Related
Determine Right Or Wrong Answer From Option Button Group
I am working on creating an examination questions in Excel. I want to also build in formulas in the Excel sheet so that the answers will be calculated automatically as the candidates choose the answers. I have inputed an option button from the froms toolbox beside each answers for the candidate to click to indicate the answers. Is it possible to calculate the correct answer or wrong answer with a formula when the candidate clicks on the option button ? Is there a formula that I can use?
View Replies!
View Related
Value To Cell Based On Option Button
I'm trying to use option buttons to create a text entry in an adjacent cell. Selecting Option Button 1 puts "GPM" in cell F23. Selecting Option Button 1 puts "lbm/hr" in cell F23 (The option buttons are from the control toolbox) I receive ""Run-time error '424': Object Required"" and first line is highlighted- here is copy of Sub FlowUnits() If OptionButton1.Value = True Then Sheets("Main Screen").Range("F23") = "GPM" End If If OptionButton2.Value = True Then Sheets("Main Screen").Range("F23") = "lbm/hr" End If End Sub
View Replies!
View Related
Changing Cell Content Based On Button
I have created two buttons in a worksheet to navigate to two other worksheets. I would like a cell to display 'YES' automatically if one of the two buttons is clicked and or to display 'NO'. I also would like to change the colors of these cells change automatically depending on which button is pressed.
View Replies!
View Related
Automatically Check Option Button Based On Cell Value
Is there a way to set up a "watch" on a cell so that if you type in a different number on a cell, OptionButton1_Click() gets activated? Example: A9 = 12.0104 OptionButton2 is active. In Cell A9 you type "25.0508". OPtionButton2 becomes inactive and OptionButton1 becomes active. I already have the buttons linked, i just do not know how to make the button get triggered if A9 changes.
View Replies!
View Related
Making A Command Button Visible Based On Data In Cell
I have a data validation list in cell D11 on sheet "Data Entry" and a command button "btnMultipleProperties" that I only want visible if "Multiple" is selected in "D11" I have the below code in "This Workbook" in VBE but it doesn't work. What did I miss? Private Sub Worksheet_Change(ByVal Target As Range) With Sheets("Data Entry") If [D11].Value "Multiple" Then btnMultipleProperties.Visible = False Else: btnMultipleProperties.Visible = True End If End With End Sub
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
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
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" 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 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
Toolbar Command Button As Toggle Button
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 Replies!
View Related
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. Sub Paste10Case() Windows("Book1.xlsm").Activate Selection.Copy Windows("Book2.xlsm").Activate Range("B1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=True Selection.Columns.AutoFit ActiveSheet.Select 'If ActiveSheet.OLEObject.Name "Click here to print" Then ActiveSheet.Buttons.Add(500, 1000, 75, 40).OnAction = "PageSetup10" ActiveSheet.Shapes("Button 1").Select............................
View Replies!
View Related
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)
View Replies!
View Related
Circular Reference - Value Of That Variable Changes Depending On The Location Of The Cell
Excel 2003 > Attached is a small model of what I am trying to accomplish. Cells B2 and B4 contain the same formula … a formula that calls a simple function. The function has a variable passed to it … and the value of that variable changes depending on the location of the cell. Now, see the function in Module1 … it is called CellCalc. If the variable passed = Jim then value = 3. If the variable passed = Jack then value = 8. That is straightforward. Note also that when Sheet1 is activated, I calculate the cells from left to right and top to bottom … that is important. Here is the challenge. If B2 > 1 then I want to add B2 to B4 and set B2 to 1. I can set B4 properly but I cannot reset B2. You can see my 2 attempts that are commented out. Is there some way of accomplishing this … or am I simply stuck in a circular reference? The alternative to this is to write a function that operates externally on these cells. That will work for sure but then the values of the calculations will overwrite the functions in those cells, thereby taking away the dynamic nature of this application.
View Replies!
View Related
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() ' Range("B3:B53").Select ' Selection.ClearContents Dim nloop As Long Range("C3:K53").Select Selection.Copy Range("B3:J53").Select ActiveSheet.Paste 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 Next nloop Range("B2").Value = DateAdd("d", 7, Range("B2").Value) Range("L7").Select End Sub 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
View Replies!
View Related
Hiding A Command Button Based On Conditions
I am using Excel 2003. I have created a workbook containing two sheets. The first sheet is designed as a form for our managers to complete. The fields they are required to complete are based on the selection they choose from a drop down field (set up through data validation). I have created a command button which when you click it opens up the second sheet of the workbook asking you to complete the individuals work pattern. I am trying to hide this button so that it only appears when you select certain options from the drop down field. I have looked back through posts on here and have tried adding the VBA code to the worksheet around commandbutton1.vissible = False in an IF statement but can't get this to work. A colleague has suggested that you can't hide command buttons because they are fixed items - is this the case.
View Replies!
View Related
Fill Range Based On Button Position
I'm trying to find out how can I fill a range of cells when clicking in a Command button in Excel. The process should be: 1) The user opens my XLS worksheet. 2) Goes to the next empty row and fill some specific fields 3) Then he should fill up 15 parameters on the right as Y or N 4) In case all of them are correct instead of going one by one typing "Y" I want he clicks in a button (with VBA code associated) and then 15 cells on the right should be filled with "Y" value
View Replies!
View Related
Hide/unhide Button Based On Data Validation
I am creating a disclaimer in one of my sheets. Cell I15 contains 'Are you a resident for tax purposes?' Cell N15 has the data validation list of Yes or No. Then I have a button below which covers rows18, 19 and 20. Is there a way I can have this button hidden until Cell N15 = Yes?
View Replies!
View Related
Option Button Properties Change Based On A Checkbox
I would like to set up some option buttons so that the will be become visible and enabled when a check box is checked, and the opposite when the same checkbox is not check. This is my problem Private Sub cbpDiscAlum_Click() Dim myOption As Control Dim myValue As Boolean myValue = cbpDiscAlum.Value = True If myValue = True Then For Each myOption In pDiscounts.Controls myOption.Visble = True myOption.Enabled = True Next myOption Else For Each myOption In pDiscounts.Controls myOption.Visible = False myOption.Enabled = False Next myOption End If End Sub It is the 5th command where it gets hung up: For Each myOption In pDiscounts.Controls. I am sure I am spelling both of the names correctly: the checkbox, and the group name.
View Replies!
View Related
Click Button To Rename Sheet Tabs Based On Range
I searched a few times for the answer to my problem, using different search terms, but I've come up somewhat empty (this *might* be because I'm not up-to- speed with Excel's terminology?). Anyway, my problem is this: I have a workbook which is designed to track various data for a corporate training course ( sheets for attendance, grades, tool logins, etc). After all of these are sheets numbered 1-25 (we never have more than 25 students in a class, but usually around 20). The numbered sheets correspond to the student's number in the on the class list sheet (so, the first student would have personal "report card" information on sheet 1; the eleventh student in the class list would have their information on sheet 11, etc). What I would like to do is have some way of putting a button on the class list sheet which would look at student list and rename the sheets to correspond to the student names. For example, if the fifth student listed was 'John Doe', it would rename his personal sheet ('5') to 'John Doe'. Obviously, I would need to do it for all of the students, and IDEALLY it would then get rid of any numbered sheets which are not used (having 21 students would only rename the first 21 sheets, but then hide sheets 22, 23, 24, and 25).
View Replies!
View Related
Hide Button Or Textbox Based On Caption Across Multiple Sheets
I have a workbook with 31 sheets. Each sheet has 15 textbox button that call macros. I would like to hide 1 textbox on all sheets until another macro is called. I know how to get the textbox names to be able to hide them, But because these text boxes were copied most of the are the same name, however on severl sheets they are different names "Textbox 4 on most sheets but it could be textbox 34 on others. The ones I want to hide all have the same text label. Is it possible to get VBA to return the label text.
View Replies!
View Related
Userform Listbox That Lists Data Based On Radio Button Selections
I have a worksheet (attached) that lists various clients in columan C. Column E lists whether each of the clients listed in Column C are 'Existing Business' or 'New Business'. I require a user form that has three radio buttons (one to select 'New Business', another for 'Existing Business' & one for 'All'). When Selecting a radio button, (e.g. 'Existing Business'), I need all clients listed in Column C of the worksheet that also have 'Existing Business' in Column E to be listed in a ListBox on the UserForm (with the second radio button allowing the text box to list 'New Business' and the third to list both Existing and New).
View Replies!
View Related
Command Button To Call Macro Based On Selections In Two Combo Boxes
I have two combo boxes that both contain 7-12 separate search criteria for the user to choose from. The other includes months and the other value ranges in text form. Based on the selections, e.g. "August" from other and "increased by more than 5 %" from other, I'd like to have a command button to execute the appropriate macro. I've already compiled the macros for each occasion but I just can't figure out how to get the button to execute them. Can I use the Select Case statements? If so, how? Oh, and whether it's relevant or not, the boxes and the button are from the control toolbar.
View Replies!
View Related
Copy Range As Paste As Values Based On UserForm Option Button Choice
I have a frame (Frame1) on a userform added using Microsoft Forms 2.0 Frame. I have added option buttons to the frame named OptionButton1 thru OptionButton4. I am trying to add code where certain cells are copied and pasted depending on which optbutton is selected. I tried the following code but because the option button is a frame object it doesn't seem to trigger the event. Private Sub OptionButton1_Click() 'copy level 1 If Me.OptionButton1 = True Then Worksheets("Sheet1").Range("G10:G32").Copy Worksheets("Sheet1").Select Worksheets("Sheet1").Range("C10:C32").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False End If End Sub
View Replies!
View Related
Run Command Button Click From Another Command Button
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 Replies!
View Related
Click The Master Button, That Would Click Another Button On A Different Worksheet
Running into this issue of "transworksheet macros" I have a master "update" commandbutton on sheet 1. I have two sub "update" buttons on sheet 2 and 3 respectively. What I would like to do is have the user click the master button, that would "click" the two sub buttons so they run their respective macros on their respective worksheets (within the same worksheet. I have tried pasting the macro code in the master button, telling it to select the sub worksheet and then run the macro, but the marco runs itself on the master worksheet instead.
View Replies!
View Related
Cell Value Based On Mouse Location
Is there a way to have excel track where my mouse is hovering(say if I have it hovering over cell A4) and have it return the value of A4 in a different cell (e.g B7)? This would occur in real time - that is, as my mouse moved over the spreadsheet, B7 would update in real time. Note I would also accept someone selecting the cell - hovering (no select) would be ideal but selecting is OK.
View Replies!
View Related
Unlock A Cell With A Button
I want to let the user unlock certain cells but only after they have clicked a button. To notify that the cell is unlocked I also want it to change colour. Is this possible? I have attached a small example. Password is mg unlock.xlsx
View Replies!
View Related
Creating Variables Based On Cell Location
if Row i Column j is activated then a corresponding variable Aij, Bij, Cij or Dij would be created (if not already existing), or cumulated by 1. The problem as I see it - completely generalised - is to create a variable where the variable name itself is formulaic, as well as its value. I know SAS would have no problem with this due to the order in which it parses data steps, but I'm pretty amateur with other programming languages so I don't know if this is blindingly obvious or downright impossible!
View Replies!
View Related
|