Floating Userform on Excel Spreadsheet

[VBAhack]

Hi All,

Forgive me if I don't always use the correct terminology. I have what I call a 'floating' Userform (I think the term is modeless) on an Excel Spreadsheet with option buttons whose values change depending on the location and contents of cells that are clicked. The form is continuously visible as different cells are clicked and is normally de-highlighted until you actually click on the form. I use the Worksheet_SelectionChange event to trigger loading the values of the option buttons. Thus, as various cells are clicked, either with the cursor, or Enter key, or arrow keys, the option button values continuously change as the cells are traversed. This is as designed.

Here's the problem. Apparently at random, when a cell is clicked, the form suddenly becomes highlighted, preventing further traversing of cells unless the cursor is used to click a cell (thus de-highlighting the form). The odd thing is that this strange behavior of the form suddenly becoming highlighted occurs at random. I've found a workaround by by setting the form Enable property to False, load the form values, then set the Enable property to True. But, I can't figure out why this is necessary.

Any idea why the form suddenly becomes highlighted as I move among the worksheet cells w/ Enter or arrow keys?

[Ecniv]

Do you use the setfocus in your code on the event on cells?
Perhaps one is firing when it shouldn't?

[VBAhack]

Do you use the setfocus in your code on the event on cells?
Perhaps one is firing when it shouldn't?

I'm not using setfocus. Should I be? I understand .visible and believe I understand .enabled, but I don't know how setfocus comes into play.

[Ecniv]

Uhh not sure it actually applies to Excel (sorry!).

On Access forms, .SetFocus pulls the focus to the form/control. Thought there may be one in Excel.

Can you re-call the .Show method? Would that refloat it back up?


EDIT:
A thought on the original post - how do you fill the values?
Maybe the code is setting the focus/highlighting the field thus making the form active?

[RobDog888]

What version of Excel are you running?

[VBAhack]

What version of Excel are you running?

I'm using Excel 2002. The workaround seems to work, so I'm happy. However, I'm still curious as to the strange behavior.

On another topic, thanks again for your help with the API - it works great.

[RobDog888]

Glad to have helped and now I remember and see how this thread ties into your other thread.