Excel 2010+ Update lable when certain lable is clicked or textbox has focus

[JasonEnsor]

Hi Guys,

I have created a worksheet and attached several controls on to it, I am then locking the worksheet so that you can only click the labels or textboxes I have added(all activeX).
I have set this up so that if you click on a label or in a textbox another label on the screen shows helpful information regarding what you are looking at. Due to the number of controls though I am finding my code is starting to get long and having to duplicate information. I know I could create a load of consts and then pass the const value so that would mean I reduce the number of duplicate messages. Just wondered if you guys had any other thoughts on how best to do this. With 20 textboxes, it means I have 20 labels which means 40 subs....it just feels a little messy to me. I guess I want to be able to associate the label and textbox...maybe a class that handles both the click event of the label and the Got_Focus event of the textbox. My current Click and Get Focus code looks similar to the below. I am considering moving the text to a veryhidden worksheet instead of hard coding it.

Private Sub lblBenefits_Click()
    txtFieldName.Text = "Benefits"
    txtFieldDescription = "What is the primary benefit of the change?"
    txtFieldExample = "Revenue Generation"
End Sub

Private Sub txtBenefits_GotFocus()
    txtFieldName.Text = "Benefits"
    txtFieldDescription = "What is the primary benefit of the change?"
    txtFieldExample = "Revenue Generation"
End Sub

I know I am probably gonna have to accept defeat on this but I thought id throw it out there to see if anyone had any suggestions.

Regards

J.

 

[combo]

You may find my faq707-4976 helpful, the difference is that you have a pair of controls, so you need two WithEvents variables, and you have worksheet's module instead of userform module, so you need a different way to instantiate the collection (and replace references, userform => worksheet).
A good alternative is, as you pointed, an array of objects based on the same class . A very good tutorial about events in vba you can find here.


combo

 

[Andrzejek]

I would investigate combo’s suggestion, but also consider this approach (may not be the best, but it is a lot less coding)

Private Sub lblBenefits_Click()[blue]
    Call FillText(1)[/blue]
End Sub

Private Sub txtBenefits_GotFocus()[blue]
    Call FillText(1)[/blue]
End Sub 
[blue]
Private Sub FillText(ByRef intT As Integer)

Select Case intT
    Case 1
        txtFieldName.Text = "Benefits"
        txtFieldDescription.Text = "What is the primary benefit of the change?"
        txtFieldExample.Text = "Revenue Generation"
    Case 2
        ....
End Select

End Sub[/blue]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.