Prev: LABKHAND: Problem using INDIRECT function.
Next: running applications
From: ker_01 on 15 Jan 2010 14:25
That definitely gives me a path to follow. I hadn't posted code because I
couldn't think of which code would be useful/relevant; I use the
Combobox_change event, but the problem was occuring because I /wasn't/
changing it.

On my way home yesterday I though that one solution would be to put a
non-selectable, clear box over the data area of the combobox to prevent a
click. I'll instead do some testing and see if the click event is only
triggered by mousing into the data area, or also the dropdown selection. If
it is only the mouse event, then I can just block it or something (I hope).
If that event also occurs on the dropdown, then I'm still in murky water, as
I'm not familiar with the events and how to control them for a specific
control at this level.

Thanks,
Keith
"JLGWhiz" wrote:

> It would be easier to offer a suggestion if the CombBox code wast posted.
> However, one way to handle it is to create a handling loop where a message
> box is generated when the selection is made and the ComboBox event is
> triggered. As an example, if you are using the click event:
>
> Private Sub ComboBox1_Click()
> dblChk = MsgBox("Are you sure you want to select " & _
> Me.ComboBox1.Value, vbYesNo + vbQuestion, _
> "CONFIRM SELECTION")
> If dblChk = vbNo Then
> Me.ComboBox1.Value = -1
> Exit Sub
> Else
> 'Your current code here
> End If
> End Sub
>
> The object is to reset the ComboBox to empty without triggering any other
> event.
>
>
>
> "ker_01" <ker01(a)discussions.microsoft.com> wrote in message
> news:7FD9AAAC-09DB-42D5-A608-EE0679A58C01(a)microsoft.com...
> >I have a userform that includes a combobox which is optional for the user,
> > but if they do use it, they have to select one of the three values
> > provided
> > (no creativity allowed).
> >
> > This works fine if the user ignores the field, or if they make a
> > selection.
> > They can even click the combobox 'arrow' to see the values and then click
> > to
> > another part of the form without making a selection. They can tab through
> > controls including this one without any problem. All good.
> >
> > However, a user may enter the field (mouseclick) thinking they might want
> > to
> > make a selection, then decide against it. In those cases, they should be
> > able
> > to click on another control, tab away, or click the submit or cancel
> > commandbuttons on the form without error.
> >
> > However, the actual behavior is that once the combobox field is entered by
> > the cursor via a mouse click, a selection is required. Attempting to leave
> > the combobox without a selection results in the error "Microsoft Forms:
> > Invalid property value"
> >
> > Is there a different way to set up the combobox that avoids this error? I
> > do
> > not want to offer the user a "blank" value, because after making a
> > selection
> > they are not allowed to remove it (they can change it to another of the
> > three
> > values, they just aren't allowed to blank it out again). I'd be happy if
> > there was a way to block the mouse from selecting (setfocus?) within the
> > combobox as long as they could still select the combobox downarrow and
> > select
> > a value from there.
> >
> > Thank you,
> > Keith
>
>
> .
>
From: ker_01 on 15 Jan 2010 14:41
On further testing, I'm stuck again. The combobox_click event is not
triggered when the user enters the control with the mouse, nor is it
triggered when the drop-down is selected; it only appears to occur when a
combobox item is selected.

My idea of putting a clear control over the combobox didn't work- I used a
clear label, and I can still select the combobox right through it.

When trying to replicate the error, I found the following: clicking in and
out without any changes actually doesn't throw the error; but if the user
clicks in and types a character, then hits backspace, the user is unable to
leave the control until a selection is made, even though they have deleted
their entry- there must be some event that alerts Excel that some change has
been made, after which point the value must match.

I guess my only hope would be if I could reset that 'changed' flag, but I'd
also have to be able to intercept the error message and use that as the event
to reset the flag. I have no idea how to trap that particular error, much
less determine whether the current combobox entry is "blank", so I think I'll
just have to give users explicit instructions instead of error-proofing the
form.

Thank you,
Keith


"JLGWhiz" wrote:

> It would be easier to offer a suggestion if the CombBox code wast posted.
> However, one way to handle it is to create a handling loop where a message
> box is generated when the selection is made and the ComboBox event is
> triggered. As an example, if you are using the click event:
>
> Private Sub ComboBox1_Click()
> dblChk = MsgBox("Are you sure you want to select " & _
> Me.ComboBox1.Value, vbYesNo + vbQuestion, _
> "CONFIRM SELECTION")
> If dblChk = vbNo Then
> Me.ComboBox1.Value = -1
> Exit Sub
> Else
> 'Your current code here
> End If
> End Sub
>
> The object is to reset the ComboBox to empty without triggering any other
> event.
>
>
>
> "ker_01" <ker01(a)discussions.microsoft.com> wrote in message
> news:7FD9AAAC-09DB-42D5-A608-EE0679A58C01(a)microsoft.com...
> >I have a userform that includes a combobox which is optional for the user,
> > but if they do use it, they have to select one of the three values
> > provided
> > (no creativity allowed).
> >
> > This works fine if the user ignores the field, or if they make a
> > selection.
> > They can even click the combobox 'arrow' to see the values and then click
> > to
> > another part of the form without making a selection. They can tab through
> > controls including this one without any problem. All good.
> >
> > However, a user may enter the field (mouseclick) thinking they might want
> > to
> > make a selection, then decide against it. In those cases, they should be
> > able
> > to click on another control, tab away, or click the submit or cancel
> > commandbuttons on the form without error.
> >
> > However, the actual behavior is that once the combobox field is entered by
> > the cursor via a mouse click, a selection is required. Attempting to leave
> > the combobox without a selection results in the error "Microsoft Forms:
> > Invalid property value"
> >
> > Is there a different way to set up the combobox that avoids this error? I
> > do
> > not want to offer the user a "blank" value, because after making a
> > selection
> > they are not allowed to remove it (they can change it to another of the
> > three
> > values, they just aren't allowed to blank it out again). I'd be happy if
> > there was a way to block the mouse from selecting (setfocus?) within the
> > combobox as long as they could still select the combobox downarrow and
> > select
> > a value from there.
> >
> > Thank you,
> > Keith
>
>
> .
>
From: Libby on 17 Jan 2010 13:22
Hi Keith,

In my experience it isn't possible to place controls in front of combo or
list boxes.

A solution may be to, on the combobox change event, write the text to a
hidden textbox and use that to acquire the value. That way clicking in and
out of the combobox won't matter as it's the textbox value that is counted.
The other thing is that you say '...the user types a character and
backspaces'. If you set the combobox Style property to DropdownList, then
they will only be able to choose a value on the list and if you haven't
specified a blank then they won't be able to backspace to nothing.

Libby x

"ker_01" wrote:

> On further testing, I'm stuck again. The combobox_click event is not
> triggered when the user enters the control with the mouse, nor is it
> triggered when the drop-down is selected; it only appears to occur when a
> combobox item is selected.
>
> My idea of putting a clear control over the combobox didn't work- I used a
> clear label, and I can still select the combobox right through it.
>
> When trying to replicate the error, I found the following: clicking in and
> out without any changes actually doesn't throw the error; but if the user
> clicks in and types a character, then hits backspace, the user is unable to
> leave the control until a selection is made, even though they have deleted
> their entry- there must be some event that alerts Excel that some change has
> been made, after which point the value must match.
>
> I guess my only hope would be if I could reset that 'changed' flag, but I'd
> also have to be able to intercept the error message and use that as the event
> to reset the flag. I have no idea how to trap that particular error, much
> less determine whether the current combobox entry is "blank", so I think I'll
> just have to give users explicit instructions instead of error-proofing the
> form.
>
> Thank you,
> Keith
>
>
> "JLGWhiz" wrote:
>
> > It would be easier to offer a suggestion if the CombBox code wast posted.
> > However, one way to handle it is to create a handling loop where a message
> > box is generated when the selection is made and the ComboBox event is
> > triggered. As an example, if you are using the click event:
> >
> > Private Sub ComboBox1_Click()
> > dblChk = MsgBox("Are you sure you want to select " & _
> > Me.ComboBox1.Value, vbYesNo + vbQuestion, _
> > "CONFIRM SELECTION")
> > If dblChk = vbNo Then
> > Me.ComboBox1.Value = -1
> > Exit Sub
> > Else
> > 'Your current code here
> > End If
> > End Sub
> >
> > The object is to reset the ComboBox to empty without triggering any other
> > event.
> >
> >
> >
> > "ker_01" <ker01(a)discussions.microsoft.com> wrote in message
> > news:7FD9AAAC-09DB-42D5-A608-EE0679A58C01(a)microsoft.com...
> > >I have a userform that includes a combobox which is optional for the user,
> > > but if they do use it, they have to select one of the three values
> > > provided
> > > (no creativity allowed).
> > >
> > > This works fine if the user ignores the field, or if they make a
> > > selection.
> > > They can even click the combobox 'arrow' to see the values and then click
> > > to
> > > another part of the form without making a selection. They can tab through
> > > controls including this one without any problem. All good.
> > >
> > > However, a user may enter the field (mouseclick) thinking they might want
> > > to
> > > make a selection, then decide against it. In those cases, they should be
> > > able
> > > to click on another control, tab away, or click the submit or cancel
> > > commandbuttons on the form without error.
> > >
> > > However, the actual behavior is that once the combobox field is entered by
> > > the cursor via a mouse click, a selection is required. Attempting to leave
> > > the combobox without a selection results in the error "Microsoft Forms:
> > > Invalid property value"
> > >
> > > Is there a different way to set up the combobox that avoids this error? I
> > > do
> > > not want to offer the user a "blank" value, because after making a
> > > selection
> > > they are not allowed to remove it (they can change it to another of the
> > > three
> > > values, they just aren't allowed to blank it out again). I'd be happy if
> > > there was a way to block the mouse from selecting (setfocus?) within the
> > > combobox as long as they could still select the combobox downarrow and
> > > select
> > > a value from there.
> > >
> > > Thank you,
> > > Keith
> >
> >
> > .
> >
 | 
Pages: 1
Prev: LABKHAND: Problem using INDIRECT function.
Next: running applications