Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option buttons not responding
I've created a userform to be used as part of a survey. There will be
five questions each with five responses, represented by an option button. I've grouped the option buttons via a group name and each button is linked to a different worksheet cell. By default, all linked cells are set to FALSE when the userform is initialised or shown. The problem is this: after the first button is selected, if another button is selected it must be clicked twice to trult take effect. The first click causes the first button to clear, the second click causes the new button to show the black dot. I had expected that selecting a second button would cause the first to clear and the second to show the black dot all in one go. To test, I created an identical userform in an empty workbook and it behaved exactly as expected. I then exported the userform and imported into the workbook where I truly wanted to use the userform and ... back to the old behaviour. Does this mean it's something to do with the workbook? Any thoughs or suggestions on how I can stop this behaviour (which is just about making the userform unusable) would be appreciated. Thanks, Clive |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option buttons not responding
After serious thinking Clive wrote :
I've created a userform to be used as part of a survey. There will be five questions each with five responses, represented by an option button. I've grouped the option buttons via a group name and each button is linked to a different worksheet cell. By default, all linked cells are set to FALSE when the userform is initialised or shown. The problem is this: after the first button is selected, if another button is selected it must be clicked twice to trult take effect. The first click causes the first button to clear, the second click causes the new button to show the black dot. I had expected that selecting a second button would cause the first to clear and the second to show the black dot all in one go. To test, I created an identical userform in an empty workbook and it behaved exactly as expected. I then exported the userform and imported into the workbook where I truly wanted to use the userform and ... back to the old behaviour. Does this mean it's something to do with the workbook? Any thoughs or suggestions on how I can stop this behaviour (which is just about making the userform unusable) would be appreciated. Thanks, Clive It's possible that the workbook has become corrupted. Try exporting all its components to a new workbook and see if the problem ceases. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option buttons not responding
I don't suppose there is any Worksheet_Change or Workbook_SheetChange event
handler running? Jim "Clive" wrote in message ... I've created a userform to be used as part of a survey. There will be five questions each with five responses, represented by an option button. I've grouped the option buttons via a group name and each button is linked to a different worksheet cell. By default, all linked cells are set to FALSE when the userform is initialised or shown. The problem is this: after the first button is selected, if another button is selected it must be clicked twice to trult take effect. The first click causes the first button to clear, the second click causes the new button to show the black dot. I had expected that selecting a second button would cause the first to clear and the second to show the black dot all in one go. To test, I created an identical userform in an empty workbook and it behaved exactly as expected. I then exported the userform and imported into the workbook where I truly wanted to use the userform and ... back to the old behaviour. Does this mean it's something to do with the workbook? Any thoughs or suggestions on how I can stop this behaviour (which is just about making the userform unusable) would be appreciated. Thanks, Clive |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option buttons not responding
Just a guess...
I had some weird Optionbutton behaviour when i set "ShowModal" to False for that Userform (which, as I understand it, allows the user to edit content in the workbook while the userform is open. Unfortunately it also made a lot of my OptionButtons not work - i.e. the one selected would remain with the value "false" when I clicked my OK button. I've since set ShowModal back to "True") I haven't investigated fully why ShowModal does this weird behaviour, but it's worth looking at from your end I guess |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option buttons not responding
Thanks for these suggestions.
I did have a worksheet_change event code for a worksheet unconnected with the control sources for the option buttons. ShowModal is TRUE I've tried to rebuild the workbook from scratch: first a "Data" sheet for the control sources connected with the option buttons on the UserForm1. Works as expected. Added simple data to second sheet, named "Staff": column B header is "Name", column C header is "Staff Number". 7 sample staff names and numbers included. Userform works as expected. When I collect the staff names and numbers from a central source, some of the numbers are actually labels so column D header is "Numerical Staff Number" and I have a formula to check if the column C entry is a value, and convert text entries to values. The cell formula for row 2 is: =IF(ISBLANK(B2),"",IF((CELL("type",C2)="v"),C2,VAL UE(C2))) The first ISBLANK is present becaused I hope to make this as dynamic as possible due to many staff changes :-( As soon as one instance of this formula is included in the "Numerical Staff Number" column the userform misbehaves. I've changed the formula to: =IF(ISBLANK(B2),"",IF(ISNUMBER(C2),C2,VALUE(C2))) This formula does not cause the userform to misbehave. Does this point to somehting about the CELL() function? Any other thoughts? All contributions appreciated, thanks, Clive |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option buttons not responding
Thanks for these suggestions.
I did have a worksheet_change event code for a worksheet unconnected with the control sources for the option buttons. ShowModal is TRUE I've tried to rebuild the workbook from scratch: first a "Data" sheet for the control sources connected with the option buttons on the UserForm1. Works as expected. Added simple data to second sheet, named "Staff": column B header is "Name", column C header is "Staff Number". 7 sample staff names and numbers included. Userform works as expected. When I collect the staff names and numbers from a central source, some of the numbers are actually labels so column D header is "Numerical Staff Number" and I have a formula to check if the column C entry is a value, and convert text entries to values. The cell formula for row 2 is: =IF(ISBLANK(B2),"",IF((CELL("type",C2)="v"),C2,VAL UE(C2))) The first ISBLANK is present because I hope to make this as dynamic as possible due to many staff changes :-( As soon as one instance of this formula is included in the "Numerical Staff Number" column the userform misbehaves. I've changed the formula to: =IF(ISBLANK(B2),"",IF(ISNUMBER(C2),C2,VALUE(C2))) This formula does not cause the userform to misbehave. Keeping the new formula I moved to the next column, a sorted version of the numerical staff number list - this will be used to provide validated data to a cell from which staff will select their staff number prior to completing the survey. I create the sorted staff number list using the formula: =SMALL($D$2:$D $87,ROW()-1) The userform behaves. If I change the formula to: =SMALL(OFFSET($D$2,0,0,$I$1,1),ROW()-1) where $I$1 holds the number of non-zero entries in column B, the staff name column, then the userform misbehaves once more. So the behaviour of the userform appears, to my simple mind, to be dependent on cell formulae in worksheets. Can anyone shed light on this behaviour? I can submit a simplified version of the workbook if anyone is interested. All contributions appreciated, thanks, Clive |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option buttons not responding
ajlowndes explained on 11/4/2010 :
Just a guess... I had some weird Optionbutton behaviour when i set "ShowModal" to False for that Userform (which, as I understand it, allows the user to edit content in the workbook while the userform is open. Unfortunately it also made a lot of my OptionButtons not work - i.e. the one selected would remain with the value "false" when I clicked my OK button. I've since set ShowModal back to "True") I haven't investigated fully why ShowModal does this weird behaviour, but it's worth looking at from your end I guess Just for the record... VBA boolean value TRUE = -1 VBA boolean value FALSE = 0 Userform Show method constant vbModal = 1 Userform Show method constant vbModeless = 0 VBA boolean value TRUE = '-1'. This is not the value of the VBA constant vbModal, which is '1', that is expected when specifying the Modal arg. So using... Userform1.Show TRUE '-1 is not correct syntax, and will probably cause unexpected behavior. Using... Userform1.Show vbModal '1 is the correct syntax to use, but since MSO VBA userforms are modal by default this arg never needs to be specified. Boolean value FALSE = '0', which is the same as the constant vbModeless. Either can be used when specifying the modal arg when modeless is desired. HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command Buttons not responding | Excel Programming | |||
Command Buttons not responding | Excel Programming | |||
Command buttons not responding-sticking | Excel Programming | |||
tab scrolling buttons not responding | Excel Worksheet Functions | |||
Navigating between option buttons is not selecting the option | Excel Programming |