Option Buttons & Frames

[Greg]

Hi again,

I have, with help from Gerry, successfully implemented three frames with option buttons on a userform.

However, I note that the option buttons in the top frame always default to the same position even though it is coded for a string test, to ensure that the button defaulted to the place of last use.

So that you know what I am referring to my document is attached.

Can anyone help with this?

Regards,

Greg.

[gwkenny]

I have examined what you have submitted but I'm truly baffled with regards to:

"However, I note that the option buttons in the top frame always default to the same position even though it is coded for a string test, to ensure that the button defaulted to the place of last use."

Can you please explain what you need?

I examined your select case statement for the frame in question and it does not seem logical to me

Select Case strTest
Case "Response"
OptNoResponse.Value = True
Case "Statement of Defence"
OptNoResponse.Value = True
Case "Statement of Defence"
Case Else
MsgBox "Oh-oh...something is wrong!"
End Select

You really only have 2 cases defined and they both do the same thing: "OptNoResponse.Value = True".

[Greg]

Uureka!

Thanks GW, I see my mistake clearly now and everything works well.

However, I do have another question. Is it possible to use a string test with a dropdown list? I have struggled without success so far.

Regards,

Greg.

[gwkenny]

Yes, If your "string" is a choice within your listbox, you just set the value to your string.

i.e.: Your string is: "Booga Wooga", and "Booga Wooga" is one of the choices in your list box you just do the following:

ListBox1.Value = "Booga Wooga".

If this is not a choice in the listbox list, you'll get an error.

On a side note. I highly recommend you program your Userform in the following manner:

MODULE SIDE
A) Load the form
B) Call a subroutine to initialize the form
C) Show the form
FORM SIDE
All code within the form should be concerned with User actions while the form is active only. When the User chooses the "Close" or "OK" button then control should go back to the Module.
BACK TO MODULE
D) Execute actions based on User input from the form.

I recommend this manner because you can use the same form to edit existing records/data and entering new data. It's just the initialization routine that is different.

[Greg]

Thanks GW but I should have said I was working with drop down lists and not listboxes.

Anyway, I tried once more to adapt the option button code for use with the drop down lists. I keep getting a message Compile Error: Variable not defined. I thought it was defined and I am at a loss to see what I have done wrong. Would you be so kind as to check the attached document and coment on it for me?

Regards,

Greg.

[gwkenny]

Greg:

You get the variable not defined because the module is "Option Explicit".

Which means you have to declare any variables with a DIM statement before you can use them.

The variable not defined is "STRTEST". The following at the beginning of the subroutine corrects the problem:

Dim strTest As String

AFTER you correct that problem, you are going to hit a run time error '5941'.

This is another reason why you code intialization of forms in a module instead of the code sheet behind the form. You need to step through to see where the error occurred when the code is behind the form. In module, the VBE will highlight the line causing the problem.

You should take all the initialization routines out of the form and put it in the module.

Thus your module would look like:

Sub DemoForm()
Load frmDataInput
InitFrmDataInput [*** this is a call to your subroutine that initializes the form in the same module***]
frmDataInput.Show
End Sub

The '5941' error is caused by the following line:

strTest = ActiveDocument.Bookmarks("DefaultType").Range.Text

There is no "DefaultType" bookmark.

[fumei]

Greg...."I keep getting a message Compile Error: Variable not defined. I thought it was defined and I am at a loss to see what I have done wrong. "

My bold.

I have mentioned this very thing regarding variables before, in another thread. In fact....I believe I mentioned that specific one. Why are you still having issue with this? You can not just "think" something is defined, you have to know it is. This is not hard to do...you look and see if it is.

I am not 100% sure I agree with gwkenny regarding the usage of standard code modules, and the initializing of userforms. Maybe, but in my mind...it depends.

[Greg]

Thank you both.

I have mastered the option button issue and I now understand the String Test.

Regrettably, I can't claim the same success with the drop down list. I believe that I have implemented all the necessary code in the correct place albeit not yet as proposed by GWKenny. I hope that doesn't matter for the time being.

I now get a Runtime error '9' with a message "Subscript out of range"
and the following code is highlighted wb "FeeEarnerName", FeeEarnerName(j) My new document is attached.

At one point I did have this partly working but I didn't get the "selected case" and got "True" instead. I feel that something quite different is required in this situation but I don't know what.

I hope you can help.

Greg.

[Tinbendr]

The error 9 is the fact that nothing is selected in the dropdown, but let's backup a second.

I suspect that in MatchBookmarks, you want the dropdown to equal the bookmark, correct? If so then change the select case to:


Select Case strTest
Case "CLAIMANT"
cboFeeEarnerName.ListIndex = 0
Case "BAILIFF"
cboFeeEarnerName.ListIndex = 1
Case "OTHER"
cboFeeEarnerName.ListIndex = 2
Case Else
MsgBox "Oh-oh...something is wrong!"
End Select


You were actually making the drop value = "True", literally.

I added a test for the dropdown, just in case, in Cmdok_Click
j = cboFeeEarnerName.ListIndex
If Not j Then...

[Greg]

Many thanks Tinbendr,

I have been struggling with this for quite some time and your code is exactly what I needed.

Regards,

Greg.

[Greg]

Actually Tinbendr, I'm not quite there yet.

I have implemented your recommendations into another document containing two drop-down lists. Whilst I don't get any error code messages, I don't get the desired result insofar as the stringtest is concerned. The document I refer to is attached.

Indeed, upon running the macro I get the default message "Oh Oh something is wrong" before the Userform inputs are implemented throughout the document.

Furthermore, when I run the macro a second time, I note that the drop-down lists do not show the selection made from the previous use.

I have messed about with this quite a bit and I have concluded that further changes may be required if more that one drop-down list is employed. Sadly, I don't know what those changes are.

Once more, I sincerely hope you can assist me.

Regards,

Greg.

[Tinbendr]

I don't get the desired result insofar as the stringtest is concerned.
You only defined strTest one time, yet you attempt to test it using the Select case with the same variable.

So for each Select case you have to change the variable strTest to test for different conditions.

strTest = UCase(ActiveDocument.Bookmarks("FeeEarnerName").Range.Text)
strTest = UCase(ActiveDocument.Bookmarks("OverUnder").Range.Text)
strTest = UCase(ActiveDocument.Bookmarks("DefaultType").Range.Text)

I also added the Upper case function to prevent a possible error when the cases don't match. (OTHER vs Other for example.)



Indeed, upon running the macro I get the default message "Oh Oh something is wrong" About that, when you place multiple traps for errors, it helps to name each one according to it's function. Then you can focus on the problem instead of searching for it.

MsgBox "Fee Earner Name Error"
MsgBox "Over Under Error"
etc.


Furthermore, when I run the macro a second time, I note that the drop-down lists do not show the selection made from the previous use.I'm not sure I follow this one. Dropdowns don't keep the previous selection. They reset each time you run them. If you need to keep the info, then you'll have to store it somehow.


I have concluded that further changes may be required if more that one drop-down list is employed.
Well, too, I don't know exactly how you're using this form. If it were me, I would move this into a template and create a new one every time. However, if you're revisiting the form later to update/change it, then it may need to stay as it is.

New form attached.

[Greg]

Well Tinbendr you have definitely improved things significantly.

However, I don't agree with your statement that "Dropdowns don't keep the previous selection. They reset each time you run them." In fact the main benefit of the string test (for me) is to see what choices have been previously made. That way, if the document is amended, new errors aren't created inadvertantly. You alluded to this when you said "... if you're revisiting the form later to update/change it, then it may need to stay as it is."

Indeed, two of the three drop downs on this Userform do retain their previous selection. Only the drop down cboFeeEarnerName (Bailiff, Claimant, Other) is causing difficulty. If you make changes to that drop down and press OK you'll see what I mean. The selection "OTHER" is retained but the other two selections are random and are not retained upon subsequent use.

I should also mention that the interest calculation code that I moved from the Userform to Module 1 no longer functions. I think that code will have to be moved back to the Userform.

So, it looks like we are almost there but not quite. I have looked at the code but I can't see the error and would therefore be grateful for your further input.

Regards,

Greg.

[Tinbendr]

I don't agree with your statement that "Dropdowns don't keep the previous selection.I was thinking about that statement later on in the day, and I realized that you are storing those as bookmarks. (But dropdowns are not set to a selection (-1) when initiated until you set them, which is what you are doing.) Ok I'll give you that one. :)


the drop down cboFeeEarnerName (Bailiff, Claimant, Other) is causing difficulty.

and

the interest calculation code that I moved from the Userform to Module 1 no longer functions.

OK I'll take a look at it.

[Tinbendr]

OK, I think I've got it.

In the MatchCurrentBookmarks-Select case Statements, we had the order of the Service Method switched on the Baliff-Claimant. Ordered them as required and all seems to work properly.

Fixed the interest calculation (Varcheck). You have to add full qualifiers to get it to work.

frmDataInput.txtAmount.Text instead of txtAmount.Text

Added a format function for the txtAmount.Text so the format would be right in case you didn't enter it properly on the userform.

There are lots of these little things you can add to make it look pretty.

I assumed that once the object errors were fixed, the math was correct. I'll leave that to you to double check that.

I created one more version with a Calculate command button on the userform. (Be warned though, you have to move the userform around a little to make the numbers on the sheet update properly.)

Enjoy!

[Greg]

Thanks Tinbendr. Your approach is very interesting. I also managed to make it operate. Mine is attached for comparison. I only wish I could get a Multi Line and Word Wrap effect for the long drop down list so that it could be shortened. Perhaps you know how that might be achieved.

Greg.

[Tinbendr]

I only wish I could get a Multi Line and Word Wrap effect for the long drop down list so that it could be shortened.Yeah, that's a limitation of combo and listboxes. Well after all, it's a list. :dunno

Well, you could use option buttons and use the Caption to store the long phrase. If the phrases stay the same, this might work.

You could give the combobox list nicknames or shortened phrases such as Missed two payments, Payment Order overdue, Other. Then when the code processes the Userform, you substitute the long phrase to the bookmark.

I'll take look at it this weekend.

[Tinbendr]

Maybe a extra label. Then we wouldn't have to start over by implementing option buttons.

See attachment.

[Greg]

Thank you very much Tinbendr. Lateral thinking saves the day.