View Full Version : Disable / Enable controls in VBA ?
debauch
02-28-2006, 07:17 AM
Hi there,
I have code that works to enable/disable all textboxes on a form in vb.net, however it does not seem to work in VBA.
Dim o As Object
For Each o In Me.Controls
If TypeOf o Is TextBox Then
DirectCast(o, TextBox).Enabled = True
End If
Next
Is there a way to do this in VBA? I have run out of resources and failed after many attempts to manipulate this for VBA.
P.S - ultimately I am looking to have this work for checkboxes rather than textboxes. I have redundant code trying to uncheck 350 checkeboxes, and need to reduce program size. Can anyone help?
Daniel3581
02-28-2006, 07:34 AM
Have you named these checkboxes in any sequential order? I had the same issue when using a form in VBA, and all I did was run a quick loop through all the checkboxes to uncheck them (eg Chk01, Chk02).... I have been told that this is a very bad naming practice to use, but it saved me a lot of time :rotlaugh: Unfortuanatley, if you haven't named them like this, I can't think of anything that will uncheck them all.
Hope this helps.
tpoynton
02-28-2006, 08:26 AM
this should do it for checkboxes:
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "CheckBox" Then
Ctrl.Enabled = False
End If
Next Ctrl
using typename "TextBox" will disable textboxes.
EDIT: just read the OP again; if you want to uncheck the checkboxes, using Ctrl.Value = False should uncheck them. Setting .Enabled = False makes it so that the user can not make any changes, which doesnt seem to be what you were looking for...
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If TypeName(Ctrl) = "CheckBox" Then
Ctrl.Value = False
End If
Next Ctrl
debauch
02-28-2006, 10:03 AM
Daniel, I just left the default names 'as is'. The names of the checkboxes were not inportant. // What does your loop look like ?
typontn, The 'ME' reference is not recognized in vba. It states, "invalid use of ME keyword"
Is there anything specific I need in the Sub procedure line (by val ...etc)
tpoynton
02-28-2006, 10:17 AM
"Me" will only work if the code resides in the userform; if you have the code in a module, changing that line to the name of the userform you are trying to clear checkboxes on should work - like "yourUserFormName.Controls".
if that doesnt work, please post a sample workbook and I am sure it will get resolved quickly!
debauch
02-28-2006, 11:57 AM
It is in a module, and the checkboxes are on frames. I will mess w/ it a bit. If I cannot come up w/ anything, I will be back soon =>
mdmackillop
02-28-2006, 01:36 PM
Hi
I made up this checkbox utility for worksheet items here
http://vbaexpress.com/kb/getarticle.php?kb_id=114
The following code will clear the boxes on the worksheet. Maybe this will show a way forward.
Regards
MD
Sub CBClear()
Dim MyRange As Range
Dim cbObj As OLEObject, X As Object
Dim ctlName As Variant
Dim MinL As Double, MaxL As Double
For Each X In ActiveSheet.OLEObjects
If X.ProgId = "Forms.CheckBox.1" Then
X.Object.Value = False
End If
Next
Set MyRange = Nothing
Set cbObj = Nothing
Set X = Nothing
End Sub
tpoynton
02-28-2006, 03:43 PM
I think I erroneously assumed that the "form" was a userform; had i read a little bit more closely, I would have realized that 350 checkboxes would probably not fit eloquently on a userform...
mdmackillop
02-28-2006, 03:51 PM
I think I erroneously assumed that the "form" was a userform; had i read a little bit more closely, I would have realized that 350 checkboxes would probably not fit eloquently on a userform...
Maybe they use a very small font and very strong glasses!
debauch
02-28-2006, 04:03 PM
It it many frames, and in each frame there is 10 - 20 checkboxes. There is a listbox on the side, and when thjey select an option from the list box, the appropriate frame appears. I think the frames might be the source of my issue. THe loop you posted tpoynton, I think only works if they are on the frame itself, the code in the userform too. // Mdmackillcop , I will fiddle w/ your also, see what I can do.
tpoynton
02-28-2006, 04:19 PM
VERY funny MD!
Debauch, if you are not able to get MD's solution to work, posting a sample workbook will be very helpful - and will help me learn something too!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.