PDA

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!