PDA

View Full Version : Solved: User form- Need grayed-out options



jwise
10-10-2007, 06:43 AM
I have a userform which displays the 12 months. The user selects one (the list is mutually exclusive). The problem is that some of these months are "invalid", i.e. the data for them has not been collected. For example, in October, only the months January-September would be valid because month end data for October to December has not been collected.

It is very easy for me to decide before I do the "show" on the userform exactly which months are valid. I believe that there is a field within this form I can modify to cause the non-available months to be grayed-out. How do I do it?

TIA

Norie
10-10-2007, 07:14 AM
How exactly are you displaying the months?

If it's in a listbox/combox you can't do what you want I'm afraid.:)

jwise
10-10-2007, 10:12 AM
I am using option boxes. Each month is named like optJan, optFeb, ..., optDec. I have done some further research (this is the second time I have needed this functionality- I could not find it before) and found a field called "Enabled" with a value of True/False. Assuming the form name is frmMonth, can I use the following to gray out the month?
frmMonth.optDec.Value = False


Assuming this (or something very similar works) it would really be nice if I could set all the months' values to False in the form designer, and then enable them as the data is collected. If I use the above logic, will the data be saved so that the next time the userform is displayed, the month will be selectable?

Norie
10-10-2007, 10:18 AM
Well you could use code like that but you aren't actually setting the Enabled value.:)

Try something like this.

Me.optDec.Enabled = False

Using this syntax this would need to be repeated 12 times.

So why not try a loop?

For I = 1 To 12
Me.Controls("opt" & Format(DateSerial(2007,I, 1), "mmm").Enabled = False
Next I

jwise
10-11-2007, 06:07 AM
Thanks for the suggestion.

You said "Well you could use code like that but you aren't actually setting the Enabled value." I do not doubt your statement, but I have no clue as to why your statement is true. Will you explain this?

The code snippet:

For I = 1 To 12
Me.Controls("opt" & Format(DateSerial(2007,I, 1), "mmm").Enabled = False
Next I


I have no idea how you knew this... Where did this construct come from? Also, when I saw this, immediately wondered about a "With" loop. Would this work as well?

In a later attempt at this problem, I changed the "Enabled" value in the userform definition to "False". Then I added code that changed the value to "True" when the monthly summary data was built. However, the next time I brought it up, the months were "grayed-out" again. I'll try this again changing the "frmMonth.opt..." to the "Me.opt...".

Thanks again.

My procedural programming language methodology is deeply engrained in my being. Thanks again for your illumination.

Norie
10-11-2007, 06:17 AM
Well all this was doing was setting the Value property, not the Enabled property.


frmMonth.optDec.Value = False

jwise
10-11-2007, 07:32 AM
Thanks again Norie.


Compare the two statements:
frmMonth.optDec.Enabled = False
frmMonth.optDec.Enabled.Value = False


Actually I made a typo, as I meant to use the second statement instead of the first. It's very confusing to me that some of the operands need the ".Value" to change their contents, while others do not use this construct. How does one know when a variable needs the ".Value" or not?

Norie
10-11-2007, 08:59 AM
Well this just won't work, it won't even compile.

frmMonth.optDec.Enabled.Value = False
Do you have Intellisense enabled?

If you do as soon as you type the dot after frmMonth you should see a list of the form's properties, methods and objects ie controls.

That should include optDec.

Now when you put the dot after optDec, again you should get a list, this time of the controls methods and properties including Enabled

Now if you put a dot after enabled, what do you get - nothing.

Paul_Hossler
10-11-2007, 09:08 AM
'this will dim the control
frmMonth.optDec.Enabled = False

'this will clear the check
frmMonth.optDec.Value = False

'this will set the check
frmMonth.optDec.Value = True

jwise
10-11-2007, 10:50 AM
I tried the "...Value" and did receive an error. The "intellisense" was news to me. Seems I had seen this in a menu somewhere. It's a classic case- I knew all the options on all the menus in ISPF/PDF (mainframe editor), but I haven't done that yet on VBE. Norie has given me a job.

Norie: When I changed my "frmMonth.optDec..." to "Me.optDec..." I got a compile error- "Invalid use of Me keyword".

Paul: I do not understand what "check" is. I originally put "Value" in the code thinking that it was an "object requirement". I can not think of a good example right now, but I have several statements like "object.varname.Value = ...". I'll do some work on this.

Thanks again.

Norie
10-11-2007, 10:52 AM
Where is the code located?

jwise
10-11-2007, 12:03 PM
I made it work with your advice. Thanks so much. Here is a snippet of the event code that makes it work. The requirement was to only allow selection of the months where the data was already collected, i.e. in June you really only have Jan-May's data because the data is collected at the end of the month:
Dim i As Integer

For i = 4 To 15
If Cells(i, 11) = "Y" Then
Select Case i
Case 4
frmMonth.optJan.Enabled = True
Case 5
frmMonth.optFeb.Enabled = True
Case 6
frmMonth.optMar.Enabled = True
Case 7
frmMonth.optApr.Enabled = True
Case 8
frmMonth.optMay.Enabled = True
Case 9
frmMonth.optJun.Enabled = True
Case 10
frmMonth.optJul.Enabled = True
Case 11
frmMonth.optAug.Enabled = True
Case 12
frmMonth.optSep.Enabled = True
Case 13
frmMonth.optOct.Enabled = True
Case 14
frmMonth.optNov.Enabled = True
Case 15
frmMonth.optDec.Enabled = True
End Select

End If
Next i

The loop goes 4 to 15 for months January-December because there is a header and two blank rows. The "Y" is put there by the data collection routine. It is initialized to "N" when the worksheet is built, and changes as the data is collected.

This mechanism resets all the months every time; I can't "save" the new value of "Enabled". I do not understand this.

Thanks again for the help.

Norie
10-11-2007, 12:20 PM
I'm sorry but I don't understand either.:bug:

What's the logic behind that code?

david000
10-11-2007, 02:54 PM
If you have Listbox...


Private Sub ListBox1_Click()

Dim aDate As Integer

aDate = ListBox1.ListIndex + 1

If aDate >= Month(Date) Then

MsgBox "This month ain't done yet!"

Exit Sub

End If

End Sub

jwise
10-12-2007, 07:01 AM
Logic of posted code- In the form definition, all the months (which are defined as "option box", meaning each has a "radio button") have the "Enabled" set to False. This makes sense in that initially there is no data for the months. As the collection process continues (data is collected weekly), eventually all the data for a month will be present. The code which adds the weekly data calls a subroutine which checks to see if all the data is present for a month, and if so, it calls the routine which prepares the monthly summary worksheet. It also changes a value in the "Date" worksheet that indicates that the monthly worksheet is built.

The code you see is part of the routine that is invoked before the "monthly" form (frmMonth) is displayed. It looks at the "Date" worksheet, and changes the "Enabled" for those months which are fully collected. It checks all months and resets the "Enabled" value to True each time because the change is lost once Excel is closed. I would have preferred that once I set the month's "Enabled" to True, that it would remain.

I did a test with some data for 2006 (all available), and it correctly installed all the data. There was a data problem with one week in June so when I displayed the month's data, June was missing. I initially thought this was a bug, but a careful examination of the data revealed that there was a typo in the "imported worksheet name" (the weekly data comes from another Excel workbook prepared by another person). Once I corrected this, it handled the data as designed, and all months were available. So this is working. I just don't know if the design is very good.

The code is located in the "preceding" form object. It is there because this preceding object is in actuality a menu which contains 6 "command buttons". If you select a command button that needs to know which month to use, then this code will display the month selection form, but first it sets the "Enabled" to True for those months whose monthly summary worksheet is available.

As to putting out a message that the month's summary worksheet is not available, I did not like that. The reason is that when I tested the code, I asked for December knowing that it was not there, and I went into the debugger with a "subscript out of range" error. I knew I could include an error routine to intercept this and display the message, but I thought it was a better idea to only allow selection of those months that were available.

Thanks again to all responders- your insight is educating me.

Norie
10-12-2007, 07:58 AM
Still not quite following but how about this?


Private Sub UserForm_Initialize()
Dim I As Long

For I = 4 To 15
Me.Controls("opt" & Format(DateSerial(2007, I - 3, 1), "mmm")).Enabled = Cells(I, 11).Value = "Y"
Next I
End Sub

jwise
10-12-2007, 08:44 AM
Norie- I don't know what I did wrong, but I got compile errors when I put that code in the subroutine. I used your loop instead of the "Select Case" logic. The message was that the "Me" keyword was improper. The code was shorter and easier to read.

I am guessing that the reason it did not work is that the actual "active" userform was the "frmIntro". This is the userform with the menu. If I choose an option that requires a month selection, then the "frmMonth is displayed. Since I need the "Enabled" changed BEFORE the "Show", I think the "Me" reference is pointing to "frmIntro" and not "frmMonth". This makes the "optJan ... optDec" variables invalid, and the error message results.

I really liked the code but I haven't been able to use it. It reminds me of Rexx where you can define several variables like "abc1, abc2, ..., abc10". Then you can say "abc.i" where i is in a loop and get all the variables. The "dot" is a concatenation operator for Rexx.

It seems that multi-level userforms tremendously complicate the logic and the library requirements because the "event handler" code must be in the "form" object. It seems that things would be much simpler if you could put all the event handler code in a single object. In fact, I wish all the code was in a single place.

Thanks again for your input. I apologize for not being very clear about this.

Norie
10-12-2007, 08:49 AM
Why not make it easier on yourself and don't use multiple forms?

All along I've been working on the assumption that you wanted to enable/disable these option buttons when you opened the form they were on.

Hence the Initialize event code.

I'll need to have another look through post #15.

jwise
10-12-2007, 03:45 PM
I don't see how the multiple forms could be reduced to a single form without a serious screen clutter problem. I wholeheartedly agree that this complicates the design considerably. The real reason I went this route is that I thought this to be more 'professional' and something that I should understand anyway.

I apologize for doing it the hard way.

Here's a little compromise question. Let's assume there is only 1 userform, and it has the 12 months on it. The VBE created userform has "Enabled False" so all the months will show as being grayed out. What I want to do is to change this "Enabled False" to "Enabled True" such that the userform will display in a later invocation with the modified months visible. Can you do this? It would be very simple for me to create a new macro with just this userform and test this capability.

Norie
10-12-2007, 09:10 PM
So why not use the Initialize event?

jwise
10-15-2007, 06:23 AM
The initialize event would work, no doubt. I see no advantage to using it, because the ability to display the months comes through the command menu, and in many cases, the changes made to the available months list would not be displayed. This first menu presents 6 commands, two of which need the month to operate. Another of the commands ("Update") is used probably 80% of the time, and no month is needed for this command.

My idea was to only update the month list if I was going to display the months. I still wish I could change "Enable" to True, and the new value remain across invocations of Excel. When I change it now, the updates are lost when Excel shuts down.

Thanks for the suggestion.

Norie
10-15-2007, 06:30 AM
You see no advantage of using something that will work and something that is the logical solution.:dunno

If you want to keep values across invocations (whatever you mean by that :) ) of Excel then you would need to store the values somewhere.

jwise
10-15-2007, 07:01 AM
I apologize for my poorly worded reply. The "change months code" now works without using "event code". So the question becomes "Which solution is better?" My analysis is that the current implementation is better because the available month list is only updated if it is needed. If the "event code" was used, on most invocations, the changes it made would not be displayed.

Remember that I changed the userform's initial value of "Enable" to False for all 12 months. My intention was to reset this value to "True" in the code that actually created the monthly worksheet. The normal scenario is to add the weekly data. There are four or five sets of this data (this depends on the number of Fridays in the month). When the full month's data is collected, then I build the monthly worksheet. I wanted to add code to this routine to alter the "False" to "True". If you do the "Update" function, and then shutdown Excel, and restart it later, the change to "True" (which causes the month to be available for selection, i.e. not "grayed-out") is LOST. Thus I made the choice that if either menu selection was made which required the "month display" (a separate userform called frmMonth), I would alter the value of "Enable" to True as appropriate.

Since you obviously know more about this than I know, I am interested in understanding why you think the change event is better. Thanks for all of your input.

Norie
10-15-2007, 07:07 AM
What change event?:huh:

jwise
10-15-2007, 07:24 AM
Sorry... I meant Initialize event