Excel 2010 :: Identifying Location Of Form Button
Mar 12, 2012
I am looking for some code which will return the location of the form button which is being clicked on to activate the macro. I would like to avoid using an active.x button if at all possible.
In this way I can use the same macro on a column of buttons to trigger an action based on adjacent cell contents.
e.g.
Name Button
Mr a Button1
Mr b Button1
Mr c Button1
The aim is to use the button on an index tab and to capture the name next to the button as a sort string for data on a separate tab.
I am running Excel 2010 on Windows Xp.
View 4 Replies
ADVERTISEMENT
Jul 23, 2014
I am imagining a spreadsheet with the equivalent of an info button in each cell of column A. The idea is the user clicks the button to open a User Form containing additional info about the entitity represented by values on that row.
E.g., Row 1 is labels, row 2 is all about Bob, 3 is about Jane, 4 is about Freddy. If the user clicks the info button on row 3 then a user form opens up and displays additonal info about Jane.
I am wondering if there is a way for VB to identify which row the clicked button was on without having to write individual code for each button telling VB which row the button was on.
I am hoping there is some form button property I can reference or interrogate which will give me a cell address; a button.position property or similar. This will obviously save me a lot of coding if VB can dynamically determine which row is being interrogated as there will be a couple of hundred of these to start with, potentially thousands. (I realise Access is a better solution for database interrogation, but I have to work with excel at present).
Ultimately, the user form that the button calls up will have a list box on it and I want the list box to default to the entity on that row, and allow the user to browse the other entities if they want to.
If there isn't a solution for this I will just have a single info button for the users to click that opens the user form and then they can select the entity from a list box. But I would like to offer the "intelligent display" option but don't relish coding the equivalent of several hundred "You clicked button four hundred and seventy six, default to info card for bert".
View 4 Replies
View Related
Aug 5, 2013
Excel 2010. How/where to define userform object?
Button on worksheet has following code:
Sub Button1_Click()
Load UserForm1
UserForm1.Show
End Sub
Execution of this code generates following error:
Run time error 424
Object required
How/where to define userform object?
View 1 Replies
View Related
Mar 6, 2014
I am running excel 2010 with windows 7. I created a macro in sheet 1 and I wish to activate the macro from sheet 2 using a form button. I have entered the code below. I know how to perform this function on a more simple macro like adding names to cells. This code is a bit more complex I just dont know where to start.
Code:
Sub LoanData()
'
' LoanData Macro
[Code].....
View 3 Replies
View Related
Aug 16, 2014
I am building a code based on a command button in a row, which will check is a worksheet exists (message), if not create a worksheet from a template (from another worksheet specific to a on a cell value in the same row), rename the worksheet based on a cell value in the same row.
I am having some success for each task with exception to relative cell values .....
As the code will be specific to the row (one button per row) Questions:
how to i determine the location of the button that is clicked? (I assume once this is established i can use to pull values in the same sheet on certain columns....?)
View 2 Replies
View Related
Nov 19, 2012
I am using xp pro, excel 2010 and the ftp server has linux installed. To open files from server I press open button in excel, then choose ftp location from drop down menu. FTP logon box asks password with the option of default user selected. I provide password and go on.
Some days ago something happened, I don't know what but as a result of it when I opened the FTP logon box, the anonymous option was selected by default. So I selected user option, gave id password and hit ok. It did not connect. Then I removed that ftp location from excel ftp locations. Whenever I tried to add an ftp location, excel restarted stating error message " *** encountered a problem *** ". So I reached a site after some googling and was directed to add the whole ftp file location path in the "File name" box of the open dialog box. It worked and the ftp location was automatically added in ftp locations. [ any ftp setting in excel is automatically transferred to ms word as well]
PROBLEM > Now I have a different problem. I can access ftp folders and subfolders but when I select a file and click open, downloading bar at the bottom of the excel does not start and after a long wait an error message pops up " the internet address ' ftp://192.168.****/folder/subfolder/filename.xls" is not valid. [I can open ftp files using filezilla etc]
View 2 Replies
View Related
Apr 19, 2013
I'm using Excel 2010. When I go into the Excel Options, to the Save option and try to type in a specific network drive in the Default File Location: box, I click OK and then it doesn't save the changes. I close Excel down all the way and then restart a new Excel session and it keeps going back to "LibrariesDocuments..."
It doesn't seem to be just related to Excel...having the same issue in Word and Access 2010 as well.
View 3 Replies
View Related
Nov 26, 2012
I just got a new computer and upgraded to Excel 2010 and Windows 7. When I try to open a workbook in Excel 2010, my saved file paths on the left side of the screen are gone. I want to put them back in there.
In Excel 2007 running Windows XP, I would just right click in the left side menu and click "add" and now that's no longer an option.
View 1 Replies
View Related
Dec 28, 2010
I need minimize button on the excel form. How can i do this?
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
Aug 2, 2013
We just upgraded to Excel 2007 from Excel 2000 and have run into a challenge relating to labeling a form command button. It appears that the length of text for a command button label has been shortened to 31 characters when setting the name using VBA. No such limitation shows up when I manually create such a button.
Following is the code that used to work to create the button and label in VBA (Excel 2000):
VB:
ActiveSheet.Buttons.Add(2.25, 13.5, 443.25, 17.25).Select
Selection.OnAction = "PatientSelectedButton"
Selection.Name = "CheckFormButton"
[Code] .....
If I change the string I want to use for the label to 32 characters, or less, this code works (Excel 2007).
VB:
ActiveSheet.Buttons.Add(2.25, 13.5, 443.25, 17.25).Select
Selection.OnAction = "PatientSelectedButton"
Selection.Name = "CheckFormButton"
[Code] ....
I have to admit that this is the last item that I thought would break!
View 4 Replies
View Related
Mar 27, 2012
I need something that will take data from columns in one Spreadsheet and put in difference cells in a row. I know this could be done with recording a macro but the number of column will never be constant.
Below I attached examples of the Spreadsheet
Financials SpreadSheet
Need to have the data in column B to F put their respective cells in row in the
Master Spreadsheet
So we would have 5 rows.
Excel 2010ABCDEFGH5Job Number17542000250030003500Total6Date Booked01-Mar-1215-Mar-1215-Mar-1223-Mar-1223-Mar-127SalespersonJames ThorntonHoward StandenHoward StandenIan BullimoreSylvia Walton8AdvertiserNestleTalkTalkLloyds BankSkodaHonda9ProductNature ValleyBroadbandMortgageApril
[Code]....
View 6 Replies
View Related
Sep 26, 2013
(I am using Excel version 14.0.7106 and MS Office Professional 2010.)
I have a macro in which I have named a range of numbers in a spreadsheet, used the "find" function to find a particular number in that range, and now would like to copy some information into a cell in the same row as the found number. However, when I try to move over to the cell that I'm copying to, it only goes to the 1st row in the range that I have defined.
Here's the portion of code I think you need to see. Everything works...it finds a match...but then I don't know what to do from there to get the information to the correct row:
'Grab the 1st project number
Range("C8").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value "" Then
prjnum = ActiveCell.Value
Selection.Offset(0, 8).Select
[Code] ..............
View 7 Replies
View Related
Jun 18, 2012
Using Excel2010 in Windows7. I have a UserForm
1: TextBox for Input Username (txtUsernameIn)
2: TextBox for Input Password (txtPasswordIn)
3) Submit Button (cmdSubmit1)
I have a Worksheet
ColA: Username
ColB: Password
And this entire Range A:B, I have named "UserRegister"
I have code, attached to the cmdSubmit1 button on my UserForm. The purpose is to allow access to a veryhidden Worksheet "ADMIN".
The code 'works' if I enter in both the correct UserName and Password (ie: the code completes & Admin sheet opens up).
The code 'works' if I enter in the correct UserName and the wrong password (ie: code completes it's run, my msg pops up to say there is wrong input, unloads the form & sheet ADMIN stays veryhidden).
However, if I enter the wrong username (with either right or wrong password) the code breaks and I get a debug error. I know my code is obviously wrong/missing something etc - but I can't work it out! The line of code highlighted on the break, is between the *** below
VB:
Private Sub cmdSubmit1_Click()
Dim Username As String
Username = txtUserNameIn.Text
Dim password As String
password = txtPasswordIn.Text
[Code] ......
View 9 Replies
View Related
Mar 27, 2014
Every day I receive a csv file of NAMES, PLACES, ADDRESSES, IDS, SPECIAL ID (ETID), ITEMS, QUANTITY, AND OTHER INANE INFORMATION. which is a list of people from places that are ordering item(s) for each ETID)
I CONVERT TO EXCEL BECAUSE I HAVE TO CLEAN THE DATA FORMATTING, AND SPLIT A COLUMN INTO 2 (LAST NAME AND ETID ARE TOGETHER).
I need to put the items into a form, one order per ETID.
I tend to receive on NAME, PLACE, ADDRESS, sending orders for multiple ETIDS.
I don't know the easiest or best way to get the info into the order form. I have designed the form in word and excel.
View 1 Replies
View Related
Aug 23, 2013
My problem is I cannot copy from a Form Control drop down box, and I need to do this for ~10 drop down boxes in about 200 xls files. The files follow a template so the drop down button Inputs are uniform. Here is some more detail:
I’m building an Access Database so that our data (currently in many, many Excel Spreadsheets) can be efficiently queried. The old system was that when asset data was collected, the data would be entered into an Excel spreadsheet, using a Template. So for example:
Project A1234
Name
Main Street Pipe
High Street Pipe
Ref No.
12345
12346
Installation date
22/08/13
19/07/12
X Coordinate
55667
99212
Y Coordinate
77889
12364
We have hundreds of these files, but I managed to get all the files in one folder and Paste and Transpose the data from the relevant Cell Ranges in each file (using a VB sub) into ONE summary sheet. Success; or so I thought.
Unfortunately for one generation of the template (people changed them slightly every year), the template had people enter information into Drop Down Menus. Not a problem when they’re associated with a cell, but these were free floating Form Controls.. so you can’t actually click and copy from them, or call them in VB as you would a specific cell.. afaik.
So my issue is how can I extract the data from those Form Buttons? I’ll try and provide as much info as I can:
Firstly I tried to find some sort of identifier for each Form Button. The dropdowns are called Combo Boxes, (Developer tab > Insert > Combo Box (Form Control) ), and when you select them (by right clicking) and hit the View Code button in Developer Tab > Controls the following shows up in VB.
Sub DropDown44_Change()
End Sub
I’m guessing that the number is related to the button so that’s one useful thing, an identifier of sorts. There’s no actual code there though really from what I can see.. My problem is I have no idea how to extract from DropDown44!
The range of values in the drop down comes from a separate sheet where there’s just a list of the values. There aren’t any cells linked to the drop downs though. If I enter a cell in (Right Click) Format Control > Cell link: then it prints the number (e.g. 4) of the value in the range. So if the drop down options are “Monday”, “Tuesday”, “Wednesday”, “Thursday” and “Thursday” is selected, the linked cell says: “4”.
They didn’t link a cell, then I could call that cell in a VB sub and the job would be done. Unfortunately they didn’t, so I tried Recording a Macro and selecting the drop down form control.. That didn’t really work, except if I right clicked it, I got some more info from the macro VB:
ActiveSheet.Shapes.Range(Array("Drop Down 19")).Select
So is my drop down an Array? My understanding of an Array is that it’s a list of arguments.. all I get there is the name of the button. I am not experienced in VB so this is as far as I’ve got. Perhaps I can get a sub to select the button using the above line, but I need the info from it, i.e. which value is currently selected in the Drop Down.
(Using Excel 2010 but the .xls files are from ~2005)
View 1 Replies
View Related
Nov 22, 2013
I have an Excel 2010 workbook with many worksheets using hundreds of Form Control (not ActiveX) checkboxes. I need a bit of VBA to change Checkbox background color of each checkbox whenever the user checks the box. I assume this needs to be a click event? I don't even know the Checkbox property name I need to change I'm learning VBA as quick as I can
View 4 Replies
View Related
Feb 26, 2014
I'm using 2010. When I highlight rows or columns and select Group, the show (+) / hide (-) buttons would appear in the next to or above the header rows/columns.
The file I am working on is not showing these buttons. If I go to the Data tab & click "hide detail" or "show detail", it will hide my grouping if I'm in one of the cells of the grouping.
I can easily hide/unhide, but would rather group & click rather than highlight rows/columns.
View 3 Replies
View Related
Aug 27, 2012
1) I have a VBA macro MyMacro() which works fine.
The macro is assigned to a button on the w/s, and also runs from w/b Open() event and other locations within the w/b.
2) The macro displays a message just before exiting whenever it is called.
3) I’d like the MsgBox statement in the macro to be executed ONLY when the macro is run from the button.
In other words, skip the MsgBox statement when the macro is run from anywhere else in the VBAProject.
Can this be done ? possibly by inserting a statement or two before MsgBox in the MyMacro() code ?
View 4 Replies
View Related
Dec 6, 2012
Code + Button to automate certain row heights.
1. Starting in row 5 the row height is to "30"...every 17 rows after row5 to have a height "30"...next row 22 is "30" , then row 39 is "30" and so on...
2. All rows in between row height "30" will be with a row height of "11"
3. Can this be associated with a button....
4. where would i place this code.....
Version:Excel 2010
View 9 Replies
View Related
Sep 2, 2013
I am using excel 2010, i try to insert active x control command button insert but " cannot object insert" like this message came?
View 3 Replies
View Related
May 7, 2009
I'm trying to pull up a second form from a command button within a form. There's a command button in a sheet to open the first form (frmOrderInput.) Then there's another command button in that form to open the second form (frmPriceInput.)
The Module to open the first is this:
View 3 Replies
View Related
Jun 3, 2014
How to disable the default close button in a workbook not to affect in my command button close.
I tried already cancel = true but my command button close is also been canceled.
View 3 Replies
View Related
Sep 7, 2010
I am using Excel 2010. I am using a shared workbook that was originally created in Excel 2003. This workbook has a command button that is supposed to be clicked to open a userform. However, I cannot even click on the button when opening the shared form in Excel 2010. When the form is not shared, the button works fine.
I have no problems with the button when opening the shared form in 2003 or 2007.
Is there anything I can do to make the command button work while the form is shared and while using Excel 2010?
View 4 Replies
View Related
Aug 30, 2013
I'm using excel 2010 and I need a command button to add rows to the end of the dynamic table.
View 3 Replies
View Related
Jul 2, 2012
I have developed a Userform button in Excel 2010 using the developer icons, which when clicked on with my mouse it runs my super dooper macro. It works great!
But one thing I want to have happen is to have the choice of using the Enter key to start the macro or use the left mouse button
For example, I place data in (say) cell A1 and the userform button is in cell A2. When I place the data in A1 and press the Enter key, the cursor moves down to A2, but doesn't highlight the user button. When I press the Enter key again, the cursor moves to cell A3.
What I want is when I fill in the data in A1 and press the Enter key, the cursor moves to cell A2 and selects the button, so that when I press the Enter key again, it activates the macro.
View 3 Replies
View Related
Aug 22, 2012
I keep getting an automation error in excel 2010 when I click on the button that executes the macro. Excel crashes when the box appears. There is no code in the error box. Why I get this error message.
View 2 Replies
View Related
Nov 10, 2013
I am on office 2010
I am new to VBA programming so i might be making a obvious mistake. I am trying to create a macro that will insert rows depending on a cell value. I also need this to be executed from a command button rather then to occur immediately after the cell value is entered. So as a example
Lets say
Cell A2 has a value 20
Cell A3 has a value 3
Cell A4 has a value 5
when this data is entered and then a Command button poressed there should be
20 rows beneath cell A2
3 rows beneath cell A3
5 rows beneath cell A4
View 9 Replies
View Related
Jan 17, 2014
How can i use a single command button to do multiple task in Excel 2010.
First Instance I click -- It filters only With Record "Yes"
Second Instance I click -- It filters only With Record "No"
Third Instance I click -- It removes filter.
View 3 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
