VBA to recognise which button was clicked (1 Viewer)

[diberlee]

Hi,

I'm designing a form which will display a lot of employee information in a tabular layout. Users need the option to update a lot of these controls, but I want them to do that on a separate form. I'm wondering if VBA can determine the position of a clicked button within the tabular layout.

So if I have a layout like this:

| label | textbox | button | label | textbox | button |
| label | textbox | button | label | textbox | button |
| label | textbox | button | label | textbox | button |

Can I have some code which determines that, for example, the second button across on the 3rd row is clicked and read the corresponding label?

I was planning to have each button just call a function passing an argument, but since all these buttons will be labelled "update" it could get a bit confusing if I need to re-arrange the controls. So I wondered if what I describe above is possible, and which VBA functions I would need to look into if so?

Cheers
Duane

 

[Minty]

I assume the layout you described is a continuous form?

If so each record would presumably have a unique id on the row somewhere? Use that unique record to control the form being open?

 

[diberlee]

Hi,

No, it's just a single form that displays a load of details relating to a single employee.

Cheers

 

[Minty]

Well in that case the command button's must have different control names, just make sure you call them something meaningful.
The other thing you could do is group the cmd button with the label and text box, that way you'll only move them together.

 

[diberlee]

Well in that case the command button's must have different control names, just make sure you call them something meaningful.
The other thing you could do is group the cmd button with the label and text box, that way you'll only move them together.

Thought that would be the best way to go, was just hoping there'd be some sort of row/column object that might streamline some parts of the code. Thanks for your replies.

 

[smig]

create a Public function (Put it in a module)

Public Function pbFindClickedBtn(frm As Form, strCntrl As String)
    
On Error GoTo errHere


Msgbox "You clicked button " & frm.Controls(strCntrl).Name & " on form " & frm.Name


ExitHere:
    Exit Function

errHere:
    MsgBox "Error " & Err & " - " & Err.Description
    Resume ExitHere

End Function

Put this line on each button's OnClick event, on the propertY sheet:
=pbFindClickedBtn([" & .name & "].[Parent] ,'" & .name & "')

The above must be a function, though it return no value, to be used the way it's been used.

You can put this code in the .OnOpen event of he Form to save you the trouble writing the line above for each button:

On Error GoTo errHere

Dim ctl As Control

For Each ctl In me.Controls
    With ctl
        If .ControlType = acCommandButton Then
            .OnClick = "=pbFindClickedBtn([" & .name & "].[Parent] ,'" & .name & "')"
        End If
    End With
Next ctl


ExitHere:
    Exit Sub

errHere:
    MsgBox "Error " & Err & " - " & Err.Description
    Resume ExitHere

Each button has a unique identifier name.
You hale have to name them by hand so you can identify the clicked button

 

[diberlee]

create a Public function (Put it in a module)

Public Function pbFindClickedBtn(frm As Form, strCntrl As String)
    
On Error GoTo errHere


Msgbox "You clicked button " & frm.Controls(strCntrl).Name & " on form " & frm.Name


ExitHere:
    Exit Function

errHere:
    MsgBox "Error " & Err & " - " & Err.Description
    Resume ExitHere

End Function

Put this line on each button's OnClick event, on the propertY sheet:
=pbFindClickedBtn([" & .name & "].[Parent] ,'" & .name & "')

The above must be a function, though it return no value, to be used the way it's been used.

You can put this code in the .OnOpen event of he Form to save you the trouble writing the line above for each button:

On Error GoTo errHere

Dim ctl As Control

For Each ctl In me.Controls
    With ctl
        If .ControlType = acCommandButton Then
            .OnClick = "=pbFindClickedBtn([" & .name & "].[Parent] ,'" & .name & "')"
        End If
    End With
Next ctl


ExitHere:
    Exit Sub

errHere:
    MsgBox "Error " & Err & " - " & Err.Description
    Resume ExitHere

Each button has a unique identifier name.
You hale have to name them by hand so you can identify the clicked button

Not quite what I was looking for, but this looks like it will be just as useful as the imaginary function i wanted to use

I'll give it a go when I'm in work tomorrow, thanks very much

 

[nanscombe]

There is also the ActiveControl property which returns the control which is active as a control object.

Dim ctlCurrentControl As Control

Set ctlCurrentControl = Screen.ActiveControl
If ctlCurrentControl.Name = "txtCustomerID" Then
    .
    . ' Do something here.
    .
ElseIf ctlCurrentControl.Name = "btnCustomerDetails" Then
    .
    . ' Do something here.
    .
End If

And the ActiveForm property which returns the form which is currently active.

Dim frmCurrentForm As Form
Set frmCurrentForm = Screen.ActiveForm
MsgBox "Current form is " & frmCurrentForm.Name

Create yourself a module as before but instead of having to pass the form and control name you could say ...

Public Function pbFindClickedBtn()
    
On Error GoTo errHere


Msgbox "You clicked button " & Screen.ActiveControl.Name & " on form " & Screen.ActiveForm.Name


ExitHere:
    Exit Function

errHere:
    MsgBox "Error " & Err & " - " & Err.Description
    Resume ExitHere

End Function

Then to use the other function amend the appropriate line to say ...

.OnClick = "=pbFindClickedBtn()"

Rather than having to grab the label text you could use a naming strategy for the buttons, i.e. cmdUpdate_Name, cmdUpdate_Address, cmdUpdate_Title.

To grab the appropriate text you could then use the MID() function to grab any text to the right of the underscore.

 strFunctionLabel = Mid(Screen.ActiveControl.Name, 12)

If you wanted to apply some code depending on which button was pressed you could do some thing like ...

Public Function pbFindClickedBtn()
Dim strFunctionName as String, strFunctionLabel as String
On Error GoTo errHere

  strFunctionName= Screen.ActiveControl.Name
  strFunctionLabel= Mid(strFunctionName, 12)

  Select Case Lcase(strFunctionName)
  Case "cmdupdate_name"
    functionToUpdateName strFunctionLabel

  Case "cmdupdate_address"
    functionToUpdateAddress strFunctionLabel

  Case "cmdupdate_title"
    functionToUpdateTitle strFunctionLabel

' etc etc

  Case Else
    MsgBox "Function '" & strFunctionName &"' not found"
  End Select

ExitHere:
    Exit Function

errHere:
    MsgBox "Error " & Err & " - " & Err.Description
    Resume ExitHere

End Function