Set variable based upon which auto-created button was selected

[bdmangum]

I have a macro which auto-creates buttons in sheet4 based upon how many columns of data are in sheet3. Each button is auto-created to run a macro upon being clicked. The problem is that I need to know where on sheet4 the button which user clicked is located. Does a button trigger its nested cell as the ActiveCell? If that is the case this would be an easy program, however, using the activecell approach doesn't seem to work.

Here's my code which adds the buttons:

    'Inputs Some_Title button
        Dim oNewButton As Object
        Dim sTitle As String
    
        If Cells(WVar, 3).Value = "" Then
            Cells(WVar, 3).Font.ColorIndex = 2
            Cells(WVar, 3).Value = Cells(WVar, 1).Value
            sTitle = Cells(WVar, 1).Value
    
        'Sets Cell position and size variables
            Sheets(4).Activate
            Cells(WVar, 3).Activate
            CellX = ActiveCell.Left
            CellY = ActiveCell.Top
            CellH = 23.25
            CellW = 86.25
                        
            With ActiveSheet
                Set oNewButton = .Buttons.Add(CellX, CellY, CellW, CellH)
                With oNewButton
                    .Name = sTitle
                    .Characters.Text = "Some_Title"
                    .OnAction = "Some_Macro"
                End With
            End With
            
        End If

My goal is to have the macro which plays upon the button being clicked to sort data based upon which of the auto-created buttons was clicked. I think if we could simply set a variable based upon the button's nested cell it would be easy, but I'm not sure how to code that. I'm open to whatever way is easiest of tracking which button was clicked.

Thanks.

 

[SkipVought]

Hi,

Check out the TopLeftCell property of the button.

Sub Button1_Click()
    MsgBox ActiveSheet.Shapes("Button 1").TopLeftCell.Row & ":" & ActiveSheet.Shapes("Button 1").TopLeftCell.Column
End Sub

Skip,

_[red][/red]

 

[bdmangum]

Skip,

That code gives me the data I need, however, its also based upon me already knowing which button was clicked.

Do you know if the worksheet change event catches a button click? If it does then I can use the code you supplied and compare the row and column of the change to the row and colunm of all the buttons on the sheet. If it doesn't then I'm not sure if the code you gave helps me any.

 

[SkipVought]

YOU can know which button was clicked using the specific button click event.

I though your question was, what cell is the button in, that was clicked?


Skip,

_[red][/red]

 

[bdmangum]

I don't think we're on the same page Skip. I can't code a click event for each button. The macro could auto-create 100 buttons in sheet4. Unless there is a way to auto-code a click event for each auto-created button, there's no point in going down that path.

The reason I need to know what cell the button which was click is in is to make sure the macro grabs the right data. Each button is auto-created next to a name, the name then has sub-categories which the user can fill in by clicking the button which loads a user form.

The names will vary over time thus the button names will vary over time. So I need a way to have the button load a form based upon the name which that button is next to.

The problem is I don't know of a way to detect which button was clicked. I suggested tracking the button by using the cell its in, but I'm not sure that's the best method.

Did that explaination help clear up what I'm trying to do?

 

[SkipVought]

Sub AssignButtons()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        If UCase(Left(shp.Name, 6)) = "BUTTON" Then
            Select Case shp.TopLeftCell.Column
                Case 1
                    shp.OnAction = "ProcA"
                Case 2
                    shp.OnAction = "ProcB"
            End Select
        End If
    Next
End Sub

Skip,

_[red][/red]

 

[bdmangum]

Skip,

Sorry to be a pain, but I'm failing to see how this code does what I need. It would work great if I knew the name of the button ahead of time, but I don't so I can't insert that line of code. I could use a variable there, but in order to do that I have to somehow insert the name of the button which was clicked into the variable. The italics is the part is where I can't figure out how to do it. If I can figure out the italics part then I can use the code you posted and it will work great. Maybe you already showed how to do what I placed in italics. If so, could you please go through it again, with some more explanation?

 

[SkipVought]

Maybe you explain functionally WHAT you are trying to do rather than talking about HOW you want to do it with code.

Start with sheet3.

Skip,

_[red][/red]

 

[bdmangum]

Sheet3 contains a list of names. The user goes to sheet3 and can choose to add or delete a name from the list. After the user adds a name to the list a macro runs which inserts the name in the next row and column 1 on sheet4. The macro also adds a button in the same row, but in column 2. This button is given the same .name as the name in column 1 for deletion reference. When the user deletes a name another macro basically does the opposite of the first macro.

On sheet4 the user can choose to update the information listed under each name. They choose this by clicking the button in column 2 directly next to the name. This button should start a macro which loads a user form with the name as the caption of the form. The user then enters the data for that name into the form. Once they fill out the form the data is then saved to the same row (in columns 4-8) as the name.

That is what I'm trying to do. But I'm at a loss as to how to programmatically set the row to store the data in based upon which button was selected.

 

[SkipVought]

I am totally confused.

First you stated, "...based upon how many columns of data are in sheet3."

Now you are stating, "Sheet3 contains a list of names. The user goes to sheet3 and can choose to add or delete a name from the list."

A LIST is a number of ROWS in a COLUMN.

But here goes. ALL buttons get the SAME Procedure assigned

Sub AssignButtons()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        If UCase(Left(shp.Name, 6)) = "BUTTON" Then
            shp.OnAction = "ProcA"
        End If
    Next
End Sub
Sub ProcA()
 'ProcA determins WHO called
 MsgBox Cells(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row, "A").Value
End Sub

Skip,

_[red][/red]

 

[bdmangum]

Sweet! That works!

Thanks Skip!

As for the data list or column thing, I had a typo in the later post. It should have read: "Sheet3 contains three lists of names." Thus a list in each column from A:C. Sorry about that, sometimes I just type and write the wrong thing.

Thanks for the help!

 

[SkipVought]

What is the significance of three lists of names? Are they not all names?

Skip,

_[red][/red]

 

[bdmangum]

The reason for three lists is because each list is a seperate section. Basically the person's name can fall under one of three categories, and needs to be stored in that category. Thus the reason for three lists instead of one.

Appreciate the help. The Application.Caller works great for what I need to do.