Passing field name to form to set focus

Is there a way I can pass the name of a field to a routine which opens a VBA form with the focus set on that field, please?

Hi and welcome.

Were are you passing the variable from? A worksheet? Another form?

BSB

Thankyou BadlySpelledBuoy
A VBA subroutine runs through several rows on my worksheet, detects any errors in the cells on the row. So for example, it might find an error in the "Surname" column, at which point I will display my form which has numerous fields on it, but In this case I want to position focus on the "Surname" control (a textbox) within the form. Another time it might find an error in the "Occupation" column, etc etc.

I could employ a Select structure testing a string that points to the right field and set the SetFocus for each possibility, but that is a little bit cumbersome I think you'll agree.
Eg

Error detected in column "Surname".....

Select Case Field_To_Focus
Case "fSurname"
fSurname.SetFocus
Case "fOccupation"
fOccupation.SetFocus
Case "fReviewDays"
fReviewDays.SetFocus
End Select

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Lot easier to give a precise solution with the workbook - saves much time crystal ball gazing.
torachan.

It all depends on how your current code is identifying these errors.

As torachan suggests, attach a sample workbook and it'll be much easier for someone to help.

BSB

Here you go.
There should be a file attached (Example.xlsm) with a single routine which illustrates what I am trying to do.
Thankyou

What is wrong with the approach in your example workbook? Perhaps a little clunky if you have LOTS of controls on the form...

Other than that I'd give each control on the form a .Tag value equal to the relevant column number, exit the loops when an error is identified then loop through the form controls' .Tag properties until the corresponding one is found and set focus on that.

Very much a case of preference. Both would require a little effort during setup but I don't think there would be much in the way of a speed advantage over either approach.

BSB

OK, thanks.
The whole point is that it is extremely clunky because I am dealing with over 30 controls.
It's probably my fault for not explaining it properly....
I was imagining a tiny subroutine that recognised a literal that represented the name of the field to be able to set the value of that field itself!
Then there would be no need for a Select statement at all.

Into this subroutine I would pass the string representing the name of the textbox eg "txtbox1" and the function would set focus to that field.

The pseudocode for this subroutine (not real code, 'cos I can't see how!) would be
Sub AchieveFocus(fieldname_as_string)
fieldname.setfocus
End Sub

So AcheiveFocus("txtbox1") would set the focus to the field txtbox1.

That is subtly different to the following which is merely a straight setting:

Sub AchieveFocusBasic(field)
field.setfocus
End Sub

AcheiveFocusBasic txtbox1

... which gives the same result but is incapable of being called programmatically for many different fields without quoting the field literally.