Using vba to find a controls name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi all,
Can anyone help me? I want to run a double click event on 24 controls on my
form, what I want to happen is when the user double clicks a specific control
it changes the content from A to B then on the next dbl click to C and loops
back to A on the third dbl click. now I have the code for the test, a simple
select case, I can place a select case in each of the 24 controls but a
cleaner way is to call a function. what I need to know is, is there a way to
tell the system from what control the function was called.

Thanks for your time.

Mike J. Soames
 
Yes, you can pass the control as a control or pass it as the form name and
control name to the function.

Example:
Public Function MyFunction(ctl As Control) As String
or
Public Function MyFunction(strFormName As String, strControlName As String)
As String

You would then call it as:
MyFunction(Me.txtMyTextbox)
or
MyFunction(Me.Name, Me.txtMyTextbox.Name)

I tested the first example as follows:

Private Sub Text0_DblClick(Cancel As Integer)
Me.ActiveControl = TestPassingControl(Me.ActiveControl)
End Sub

Public Function TestPassingControl(ctl As Control) As String
Select Case ctl.Value
Case "A"
TestPassingControl = "B"
Case "B"
TestPassingControl = "C"
Case "C"
TestPassingControl = "A"
Case Else
TestPassingControl = "Error"
End Select
End Function
 
You will have a separate doubleclick event for each control, so from that
routine you just call your common routine passing as a parameter
Me.ActiveControl.Name

-Dorian
 
mikejs2000 said:
hi all,
Can anyone help me? I want to run a double click event on 24 controls
on my form, what I want to happen is when the user double clicks a
specific control it changes the content from A to B then on the next
dbl click to C and loops back to A on the third dbl click. now I have
the code for the test, a simple select case, I can place a select
case in each of the 24 controls but a cleaner way is to call a
function. what I need to know is, is there a way to tell the system
from what control the function was called.

Thanks for your time.

Mike J. Soames
Click to expand...

In addition to the other suggestions, if you want to use a
general-purpose function for this, you can use either the form's
ActiveControl property or Screen.ActiveControl in that function to
return a reference to the control that has the focus. That will work
for a function called in a DblClick event, so long as the control being
double-clicked is capable of receiving the focus. For example, you
could do that for a text box or a combo box, but not for a label
control.
 
You don't need a lot of code. A oneliner will do, to wit:

Me.ControlName = IIf(Me.ControlName = "A","B", IIf(Me.ControlName = "B","C",
"A"))

Put that oneliner in each control's DoubleClick event, and change the word
"ControlName" to the control's actual name.

Hope this helps,

Sam

Dirk said:
hi all,
Can anyone help me? I want to run a double click event on 24 controls
Click to expand...
[quoted text clipped - 9 lines]
Mike J. Soames
Click to expand...

In addition to the other suggestions, if you want to use a
general-purpose function for this, you can use either the form's
ActiveControl property or Screen.ActiveControl in that function to
return a reference to the control that has the focus. That will work
for a function called in a DblClick event, so long as the control being
double-clicked is capable of receiving the focus. For example, you
could do that for a text box or a combo box, but not for a label
control.
Click to expand...
 
OfficeDev18 via AccessMonster.com said:
You don't need a lot of code. A oneliner will do, to wit:

Me.ControlName = IIf(Me.ControlName = "A","B", IIf(Me.ControlName =
"B","C", "A"))

Put that oneliner in each control's DoubleClick event, and change the
word "ControlName" to the control's actual name.
Click to expand...

Ah, but if you have a general-purpose function like this ...

'----- start of example code -----
Public Function ToggleABC()

With Screen.ActiveControl
.Value = IIf(.Value = "A","B", IIf(.Value = "B","C", "A"))
End With

End Function
'----- end of example code -----

.... then all you have to do is, in form design view, select all the
controls to which you want to apply this logic, and on the On Dbl Click
line of their joint property sheet, enter:

=ToggleABC()

Then you don't even have to write an event procedure for all those
controls, and you don't have to change anything in the code or the call
to reflect which control was double-clicked.
 
Back
Top