Textbox within a Frame in UDF unable to take focus. Why not?

[KenWilson]

Hallo, community.

I have under construction at the moment a User-defined Form ("Userform3") with a large number of textboxes intended to receive currency inputs related to a variety of items of domestic expenditure (Rent, Power, Gas, Credit Card items, Sky Sport etc.), some of which I have set inside frames (hoping to be able to use the grouping of the controls to to limit the scope of things like "For Each ctrl in XYZ ctrl.Enabled = False Next ctrl).
I want the end-user to be able to cancel an erroneous input before it is accepted for processing by the rest of the programme. He/she clicks a Cancel-button which clears two Textboxes and sets the focus on the first of them (by name) ready for a renewed attempt to enter the data. The code works like treat for the boxes outside of frames but throws up RuntimeError 2210 for those in a frame.

Please can anyone explain what goes wrong with the following ?:

Sub CancelInput (Ctrl as Control)
'Ctrl.Name (in the case which is causing the bother) ="frSky"
Ctrl.Enabled = False '(intended to disable all the controls in the frame "frSky"; later just the relevant ones are to be reopened)
Dim X as long
X = 1 * (Instr(Ctrl.Name, "InputCent") + 1 * instr(Ctrl.Name, "InputEur") + 1 * Instr(Ctrl.Name, "Enabled") + 1 * Instr(Ctrl.Name, "Cancel")    + 1 ' (X resolves in this case to = 1)

With Userform3
    If Instr(Ctrl.Name, "Input") > 0 Then '(it is in fact greater than nought)
    .Controls("tb" & Mid(Ctrl.Name, 3, Len(Ctrl.Name) - X) & "InputCent") = ""
    . Controls("tb" & Mid(Ctrl.Name, 3, Len(Ctrl.Name) - X) & "InputEur") = ""
    ' The string in brackets resolves into "tbSkyInputEur", which is the correct name of the targeted textbox
   Elseif Instr(Ctrl.Name, "Elements") > 0 Then '(which it isn't)
    'irrelevant code
   End If
   .Controls ("tb" & Mid(Ctrl.Name, 3 Len(Ctrl.Name) - X) & "InputEur").Enabled = True
   .Controls ("tb" & Mid(Ctrl.Name, 3 Len(Ctrl.Name) - X) & "InputEur"). SetFocus   '!!! This line throws up the Error (BTW the ActiveControl           at this point turned out to be "cbClose", the CommandButton which steers the shutdown of the UDF.)
   .lbInputErr.Visible = False
   .lbInputErr.Caption = "INPUT BLOCKED: "
End With
End Sub

I'm sorry to present the material in this awkward form; my code is on a different device never to be connected with the internet and I wouldn't know how to transfer the code to my iMac, which is the machine I am writing this on. Any code any of you happen to send me I will have to transfer "by hand" if I am to try it out at all, so maybe you will get your revenge!
I am using Office 2016.

Thanks for reading this and for any efforts you may make to help me out.

My respects,

Ken Wilson

[georgiboy]

I was getting an error on the below line:

X = 1 * (Instr(Ctrl.Name, "InputCent") + 1 * instr(Ctrl.Name, "InputEur") + 1 * Instr(Ctrl.Name, "Enabled") + 1 * Instr(Ctrl.Name, "Cancel") + 1 ' (X resolves in this case to = 1)

So I made it:

X = 1 * ((InStr(Ctrl.Name, "InputCent") + 1) * (InStr(Ctrl.Name, "InputEur") + 1) * (InStr(Ctrl.Name, "Enabled") + 1) * (InStr(Ctrl.Name, "Cancel") + 1))

Was also getting an error on the below lines:

.Controls ("tb" & Mid(Ctrl.Name, 3 Len(Ctrl.Name) - X) & "InputEur").Enabled = True
.Controls ("tb" & Mid(Ctrl.Name, 3 Len(Ctrl.Name) - X) & "InputEur"). SetFocus '!!! This line throws up the Error (BTW the ActiveControl at this point

So I made them:

.Controls("tb" & Mid(Ctrl.Name, 3, Len(Ctrl.Name) - X) & "InputEur").Enabled = True
.Controls("tb" & Mid(Ctrl.Name, 3, Len(Ctrl.Name) - X) & "InputEur").SetFocus   '!!! This line throws up the Error (BTW the ActiveControl at this point turned out to be "cbClose", the CommandButton which steers the shutdown of the UDF.)

May not fix your error but looked as if it needed correcting, your full code below wrapped in code tags (for others to look at)

Sub CancelInput(Ctrl As Control)    
    Ctrl.Name = "frSky"
    Ctrl.Enabled = False
    Dim X As Long
    X = 1 * ((InStr(Ctrl.Name, "InputCent") + 1) * (InStr(Ctrl.Name, "InputEur") + 1) * (InStr(Ctrl.Name, "Enabled") + 1) * (InStr(Ctrl.Name, "Cancel") + 1))
    
    With Userform3
        If InStr(Ctrl.Name, "Input") > 0 Then '(it is in fact greater than nought)
            .Controls("tb" & Mid(Ctrl.Name, 3, Len(Ctrl.Name) - X) & "InputCent") = ""
            .Controls("tb" & Mid(Ctrl.Name, 3, Len(Ctrl.Name) - X) & "InputEur") = ""
            ' The string in brackets resolves into "tbSkyInputEur", which is the correct name of the targeted textbox
        ElseIf InStr(Ctrl.Name, "Elements") > 0 Then '(which it isn't)
            'irrelevant code
        End If
        .Controls("tb" & Mid(Ctrl.Name, 3, Len(Ctrl.Name) - X) & "InputEur").Enabled = True
        .Controls("tb" & Mid(Ctrl.Name, 3, Len(Ctrl.Name) - X) & "InputEur").SetFocus   '!!! This line throws up the Error (BTW the ActiveControl at this point turned out to be "cbClose", the CommandButton which steers the shutdown of the UDF.)
        .lbInputErr.Visible = False
        .lbInputErr.Caption = "INPUT BLOCKED: "
    End With
End Sub

[KenWilson]

Hallo, Georgiboy.

Thanks for your swift reaction.
Why you got an error with the X= code I don't know; but your new code will give a completely different value of X from that which I want to generate. This may then have caused the follow-up errors you got.

Thanks for tailoring the code for me. I'm sure it becomes more accessible to the community in its new dress.

BTW, I'm aware that leaving out the line "Ctrl.Enabled = False" will eliminate the error message; but my purpose is to test code which disables many controls at one swell poop - sorry, at one fell swoop - leaving just one (or a small number) needing to be re-enabled.

With appreciative thanks,

Ken W

[georgiboy]

The errors in the second part for me were caused by there being no comma after the 3 before the LEN part. I didn't run the code as I don't have time to recreate the userform.

[SamT]

Frames have their own Controls collections
Frame1.Controls("X").Value = "ABC"

[snb]

Do not use variable names that might be reserved in VBA: e.g. 'ctrl'

Your code should look like:

Sub M_CanInp(it)
  c00 = it.Name
  c01 = "tb" & Mid(c00, 3, Len(c00) - InStr(c00, "InputCent") - InStr(c00, "InputEur") - InStr(c00, "Enabled") - InStr(c00, "Cancel"))
   
  If InStr(c00, "Input") > 0 Then
    it.Parent.Controls(c01 & "InputCent") = ""
    it.Parent.Controls(c01 & "InputEur") = ""
  End If

  it.Parent.Controls(c01 & "InputEur").Enabled = True
  it.Parent.Controls(c01).SetFocus
End Sub

Since we dont' know the UF its pure guessing.

[KenWilson]

The errors in the second part for me were caused by there being no comma after the 3 before the LEN part. I didn't run the code as I don't have time to recreate the userform.

Ah, sorry, Georgiboy. I thought I had checked all the syntax - but we all know what Thought did (viz. It didn't, it only thought it did.) (Or as my grandfather used to say: "It followed a muck-cart and thought it was a wedding.")

Regards,

Ken W.

[KenWilson]

Hi, Uncle SamT,

I tried code including the frame reference "frSky" but couldn't make it work. I may have got the grammar wrong so I'll try again. The full ref will be "Userform3.frSky.Controls("xxx").setfocus, right?

With thanks

Ken W.

[KenWilson]

Good morning, snb and thanks for your contribution.

The route via "Parent" is one I hadn't thought of. I'll certainly look into that.
Your naming system is also interesting; what does the "M_" stand for? "Macro"?

Appreciate your help.

Ken W.

[snb]

My macronames always start with M_, the functionnames with F_

I hope you realize, that in my suggestion the controls you want to change have the same Parent as the control in the argument of the macro.

[KenWilson]

Hallo, snb.
Yes, I discovered that while exploring parenthood. I also came to realise that a control within a frame is a child of the frame itself rather than of the Userform. I shall have to take this into account in the code and I expect that will resolve the issue I was experiencing.
I also like your way of branding subs and functions as well as the various types of variables and constants. I shall certainly follow that example in future. So thanks for your (and SamT's) friendly assistance.



Ken W.

[snb]

If the macro is in the Userform's Macromodule:
- you can refer to any control in the userform using Me("tbInputCen001")
- or you can use controls("tbInputCen001")

If the control is in a frame/multipage
- you can use the frame/multipage controls collection
- Frame1.controls("tbInputCen001") Multipage1.controls("tbInputCen001")

The Parent of a control can be either the userform or any control that can contain controls (frame / multipage)
Frames/multipages can be nested many levels 'deep'.

[Aussiebear]

Count yourself lucky ken as snb mostly doesn't explain code or methodology. To snb, see it doesn't hurt to explain to people why you code in your own manner. You really need to keep up this new found direction.

[KenWilson]

If the macro is in the Userform's Macromodule:
- you can refer to any control in the userform using Me("tbInputCen001")
- or you can use controls("tbInputCen001")

If the control is in a frame/multipage
- you can use the frame/multipage controls collection
- Frame1.controls("tbInputCen001") Multipage1.controls("tbInputCen001")

The Parent of a control can be either the userform or any control that can contain controls (frame / multipage)
Frames/multipages can be nested many levels 'deep'.

Thanks again, snb, your much appreciated replies I find very helpful. I've also peeped into "..more suggestions" and immediately bookmarked the site for future reference.

Yours

Ken W.