KenWilson
09-14-2022, 07:30 AM
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
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