Userform Activate not firing in Excel 97

[nightjar]

Hi,
I'm capturing user input with a userform displayed in a loop, using the Userform_Activate event to clear the form before redisplaying ready for new input. This is working perfectly in Excel 2003, but on testing in Excel 97 it seems the activate event is not being triggered (and the data in the form isn't cleared). As I understand it on executing Hide, the userform should lose focus, but this isn't happening in 97 (the Deactivate event doesn't fire either). I've tried explicitly activating a worksheet immediately prior to the Show command with no effect - any other suggestions gratefully received - unfortunately I do need this to work in Excel 97! Many thanks.

[tpoynton]

try using initialize instead of the activate event...if it even existed in 97!

[nightjar]

it does... and it runs the first time the form is shown. I could use the Initialize sub but I'm already using this for all the form setup tasks that I only want to run once, so I'd need to introduce a conditional variable to check for 'first show' plus unload the form each time I want to reshow it to ensure Initialize runs. Plus I think there may be other issues. All in all too much of a hack of perfectly good code just to make it run under 97. I guess I was hoping there may be a simple reason why 97 seems to treat Userform_Activate differently in this case, such as a focus issue (I have already found this was the cause of another compatibility glitch which turned out to be a bug in 97)

[mikerickson]

One workaround would be to add the line

Call UserForm_Activate()

immediatly after the .Hide command (or before the .Show).

[nightjar]

thanks. The disadvantage there would be that the activate sub would run twice each time in versions above 97, but I may have to settle for that if all else fails. Ideally I'd like to find the reason behind the altered behaviour and create a workaround based on that...

[mikerickson]

To prevent running twice, you might test if UserForm1.Visible = True.