Macro to enable text boxes
- Thread starter reidhaus
- Start date
Macros cannot inherently enable/disable controls. I'm thinking you may be confusing "Macro" with "VBA" or "code". Even if that's not the case, you'd need VBA code to do it. It's very simple VBA code, but a Macro will not do that. Hell, if you wanted to, you could write a routine in VBA and call it from a Macro if that suits your fancy.
M
Mike375
Guest
SetValue will do enabled
Item [Forms]![YourFormName]![FieldorControlName].enabled
Expression No or Yes as required.
Item [Forms]![YourFormName]![FieldorControlName].enabled
Expression No or Yes as required.
The idea is to move away from macros. If you can write a Macro to do a SetValue, you can write it in VBA and not have 400 macros named macro1, macro2, macro3, and so on.
Your example will work, but that's not the point. If he has 25 items in his combobox and each one turns off or on a combination of text boxes, just how many macros will that take, versus just writing the code to do it? Which is easier to maintain? Which is more portable? Which is easier for other programmers to read? Which is easier to reproduce?
I'm not 100% anti-macro, but I'm close. Sometimes, an AutoExec macro is nice. Sometimes, writing a macro to run a string of 20 queries is easier than coding it. But do you really want to push that on someone?
The four "ables" typically do not involve macros. Those are portable, maintainable, reusable, and understandable. Macros rarely fit that context.
Your example will work, but that's not the point. If he has 25 items in his combobox and each one turns off or on a combination of text boxes, just how many macros will that take, versus just writing the code to do it? Which is easier to maintain? Which is more portable? Which is easier for other programmers to read? Which is easier to reproduce?
I'm not 100% anti-macro, but I'm close. Sometimes, an AutoExec macro is nice. Sometimes, writing a macro to run a string of 20 queries is easier than coding it. But do you really want to push that on someone?
The four "ables" typically do not involve macros. Those are portable, maintainable, reusable, and understandable. Macros rarely fit that context.
M
Mike375
Guest
I don't want to argue about macros etc. as I have been through that on this forum before and no one gains.
However, with respect to this thread the poster asked a question and the basis of the question is about automating enable Yes or No.
My post provided him with an answer he can use now. Your post simply told him that code was better (which is all fine)......but where is the code? With your answer he is no further in front than when he started.
As a side note, how quick it is to do the 25 control boxes depends on their names. I tend use names like 1Box, 2Box, 3box etc. One line and its conditions are made and then a couple of copy and pastes and then run down the lines and change 1box to 2box, 3box etc.
Macros are good when on the phone with someone or even sending a few in a little .mdb file with an a macro to export them. If someone is doing the macro on the phone then they can only foul up that single macro.
Microsoft has macros in Access and I believe has enhanced them for the Access 2007. Obviously coding is required for different tasks and my data base is full of it for the connections between Access and Word. Both have their place.
However, with respect to this thread the poster asked a question and the basis of the question is about automating enable Yes or No.
My post provided him with an answer he can use now. Your post simply told him that code was better (which is all fine)......but where is the code? With your answer he is no further in front than when he started.
As a side note, how quick it is to do the 25 control boxes depends on their names. I tend use names like 1Box, 2Box, 3box etc. One line and its conditions are made and then a couple of copy and pastes and then run down the lines and change 1box to 2box, 3box etc.
Macros are good when on the phone with someone or even sending a few in a little .mdb file with an a macro to export them. If someone is doing the macro on the phone then they can only foul up that single macro.
Microsoft has macros in Access and I believe has enhanced them for the Access 2007. Obviously coding is required for different tasks and my data base is full of it for the connections between Access and Word. Both have their place.
My point exactly. A quick fix is not not a long term fix. Whomever you're on the phone with needs to understand that. If your control names have no inherent meaning, you're setting yourself up for having to reinvent the wheel multiple times.
Again, I'm not 100% anti-macro, but they make bad habits like the ones you are describing. If I ever made an application with "ComboBox1", "Text6", "Checkbox3", and so on, I'd be taken aside and perhaps reprimanded.
If you want to harness to full power of Access, Macros are not the way to go. And if you're posting answers that show you know better, you know that. And finally, this is the official Access forum for MS. Considering the feats we pull off using Access and VBA, it's far more powerful than perhaps you get. I don't know that and I apologize if I'm wrong.
At the same time, I will never promote the use of Macros outside of a few specific situations where they make sense. As for the code, if he wants it, I will provide it. But many confuse "macros" with "programming" and I think the original poster may have been one of them. If I'm wrong about that, sorry about that too.
That's not the point. Macros are the bane of the non-developer (and hence there's no "Record Macro" in Access). They have their place, but far more often than not, they are the wrong solution.
M
Mike375
Guest
Lecturing me does not provide an answer to reidhaus So far the only answer he has is from me......with..........a macro
you could write a routine in VBA and call it from a Macro if that suits your fancy.
Give reidhaus the code rather than being some type of snob. Do you want me to do it for you?
You have made three postings to this thread but.....still no help to reidhau............if you don't know how to do coding......but are critical of macros......then STFU..........Again, do you want me to do it for you?
Perhaps you will be able to make a fourth posting......One that provides an alternative solution to reidhaus
Mike
you could write a routine in VBA and call it from a Macro if that suits your fancy.
Give reidhaus the code rather than being some type of snob. Do you want me to do it for you?
You have made three postings to this thread but.....still no help to reidhau............if you don't know how to do coding......but are critical of macros......then STFU..........Again, do you want me to do it for you?
Perhaps you will be able to make a fourth posting......One that provides an alternative solution to reidhaus
Mike
VB code for enable/disable text boxes
Originally, instead of option buttons I had a simple list box (not that it matters as there are only 4 choices). In the code, I had:
Private Sub List128_BeforeUpdate(Cancel As Integer)
If Me.List128 = "CT" Then
Me.A_R.Enabled = True
Me.A_V.Enabled = True
Me.A_V__Batteries_.Enabled = True
End If
End Sub
It kind of worked: the disabled boxes became enabled when the "CT" choice was clicked. However, once the choice was made and the boxes enabled, changing the choice to one of the other 3 did not revert the enabled boxes back to being disabled, nor did they revert to disabled when opening a new record.
What I was trying to avoid was writing 30 lines of "enable" code and 30 lines of "disable" code for each of the four choices. Too much room for (my) error.
I appreciate any guidance and thank those that have replied thus far.
Originally, instead of option buttons I had a simple list box (not that it matters as there are only 4 choices). In the code, I had:
Private Sub List128_BeforeUpdate(Cancel As Integer)
If Me.List128 = "CT" Then
Me.A_R.Enabled = True
Me.A_V.Enabled = True
Me.A_V__Batteries_.Enabled = True
End If
End Sub
It kind of worked: the disabled boxes became enabled when the "CT" choice was clicked. However, once the choice was made and the boxes enabled, changing the choice to one of the other 3 did not revert the enabled boxes back to being disabled, nor did they revert to disabled when opening a new record.
What I was trying to avoid was writing 30 lines of "enable" code and 30 lines of "disable" code for each of the four choices. Too much room for (my) error.
I appreciate any guidance and thank those that have replied thus far.
Reidhaus, I have to get to the office but I will give you an answer from there. It's sort of how you are describing you tried it (and it looks like I was right in that you went the code route). Mike, I didn't mean to upset you, and programming isn't a game. Telling me to "STFU" is a brilliant stroke though. You as well proved my point. GG! 
Reidhaus, the answer is going to be in writing a routine that disables all the controls at once (a subroutine you'll call) and then you'll enable the controls you need as necessary. I'll expand on that more in a bit.
Sorry about any confusion.
EDIT: OK, I'm at the office.
Since you've only got four options, the quickest way to get to your solution will be to enable controls as necessary. Since you don't want to have to write enable/disable for every single combination, a clean way to do this will be to write a separate routine that disables all your controls at once, and you then enable only those you want. Therefore, the code will look something like this:
The idea here is that you have one routine that simply disables every control, and then depending on the listbox selection, you only have to enable a subset of controls. This way, you don't have to refer to every single control every time the listbox changes, only those you want enabled. If one of the listbox options enables all of the controls, you could rewrite the "DisableAll" subroutine example to accept a parameter and reverse selections, like this:
To enable everything, call that subroutine like this:
To disable everything, call it this way:
There are numerous ways you can go about modifying this to make it more elegant (use tags, put field names in a table and cycle through that, etc.), but the important part is to have a single routine that will either enable or disable everything, and then you only touch the controls you want to alter. In other words, avoid this:
That quickly becomes cumbersome and difficult to maintain. Hope that helps.
Reidhaus, the answer is going to be in writing a routine that disables all the controls at once (a subroutine you'll call) and then you'll enable the controls you need as necessary. I'll expand on that more in a bit.
Sorry about any confusion.
EDIT: OK, I'm at the office.
Since you've only got four options, the quickest way to get to your solution will be to enable controls as necessary. Since you don't want to have to write enable/disable for every single combination, a clean way to do this will be to write a separate routine that disables all your controls at once, and you then enable only those you want. Therefore, the code will look something like this:
Code:
YourListBoxName_BeforeUpdate (Cancel as Integer)
Call DisableAll
Select Case Me.YourListBoxName
Case "A"
Me.YourControlName1.Enabled = True
Me.YourControlName3.Enabled = True
Me.YourControlName5.Enabled = True
.
.
Case "B"
Me.YourControlName2.Enabled = True
Me.YourControlName4.Enabled = True
Me.YourControlName6.Enabled = True
.
.
Case X
Me.YourControlNameX.Enabled = True
Me.YourControlNameX.Enabled = True
Me.YourControlNameX.Enabled = True
.
.
Case Else
End Select
End Sub
Sub DisableAll
Me.YourControlName1.Enabled = False
Me.YourControlName2.Enabled = False
Me.YourControlName3.Enabled = False
.
.
Me.YourControlNameX.Enabled = False
End SubThe idea here is that you have one routine that simply disables every control, and then depending on the listbox selection, you only have to enable a subset of controls. This way, you don't have to refer to every single control every time the listbox changes, only those you want enabled. If one of the listbox options enables all of the controls, you could rewrite the "DisableAll" subroutine example to accept a parameter and reverse selections, like this:
Code:
Sub EnableDisableAll(OnOff As Boolean)
Me.YourControlName1.Enabled = OnOff
Me.YourControlName2.Enabled = OnOff
Me.YourControlName3.Enabled = OnOff
.
.
Me.YourControlNameX.Enabled = OnOff
End SubTo enable everything, call that subroutine like this:
Code:
Call EnableDisableAll(True)To disable everything, call it this way:
Code:
Call EnableDisableAll(False)There are numerous ways you can go about modifying this to make it more elegant (use tags, put field names in a table and cycle through that, etc.), but the important part is to have a single routine that will either enable or disable everything, and then you only touch the controls you want to alter. In other words, avoid this:
Code:
YourListBoxName_BeforeUpdate (Cancel as Integer)
Select Case Me.YourListBoxName
Case "A"
Me.YourControlName1.Enabled = True
Me.YourControlName2.Enabled = False
Me.YourControlName3.Enabled = True
Me.YourControlName4.Enabled = False
Me.YourControlName5.Enabled = True
Me.YourControlName6.Enabled = False
.
.
Case "B"
Me.YourControlName1.Enabled = False
Me.YourControlName2.Enabled = True
Me.YourControlName3.Enabled = False
Me.YourControlName4.Enabled = True
Me.YourControlName5.Enabled = False
Me.YourControlName6.Enabled = True
.
.
Case X
Me.YourControlNameX.Enabled = True
Me.YourControlNameX.Enabled = False
Me.YourControlNameX.Enabled = True
.
.
Case Else
End Select
End SubThat quickly becomes cumbersome and difficult to maintain. Hope that helps.
Last edited:
