Excel2000 VB Not working in Excel97

[tk19]

I created a spreadsheet that uses a VB form and VB code in Excel 2000. One of my users has Excel 97, and the form will not run. I also have a button embedded in the worksheet that runs VB code directly, and that will not work either.

What kinds of things could be the problem?

Thanks,
T

[Steve Hartman]

I had the same problem. What worked for me can be found at:
http://www.mrexcel.com/board/viewtop...ic=323&forum=2

[Mark O'Brien]

One other thing to look out for with XL 2000's userforms is that they support "vbmodeless" i.e. you can take the focus from the form and still click in cells and stuff. In XL '97 this option wasn't available, but there is a way around it.

[tk19]

Update to my problem: thanks for responding Steve, however, neither person had a problem with their References. I loaded the spreadsheet on their computer, and went through the code. It had a compilation error on the Round function! I changed every instance where I had something like Round(Variable, 2) to Int(100*Variable)/100. Then it had a problem with Sheets("Sheet1").Range("myrange").Clear. I didn't really need that statement so I commented it out. Then everything worked!! I was dumbfounded.. I checked to see whether it was something like they didn't have the Analysis Tool Pak checked, but they did.. Should I report this to Microsoft or something??

Mark, I wasn't aware you could do that.. I have excel2000 and I can't click outside the form. Is there something I can do to allow myself to do that? It would be helpful in debugging...

thanks everyone..

[Mark O'Brien]

Yeah, when you do the useform.show method, you put in an argument afterwards. For example to show a Modeless version of UserForm1 use:

  If Application.Version < 9 Then

     UserForm1.Show

  Else

     UserForm1.Show vbModeless

  End If

I put it in check if we're running a version that is less than Excel 2000 (version 9.0) because this will take some hassles out in the long run.

[Colo]

Hi all. Here is only an information....
I cannot recommend my method to you, but to use bug of xl97, "vbmodeless" is possible on xl97.
Those who are interested in, let's try the following.(run test)

'---Standard module

Sub test()

  UserForm1.Show

End Sub



'---Userform1 module

Private Sub CommandButton1_Click()

' If Error message is shown, delete the comment out of following.

'  CommandButton1.TakeFocus******* = False

'  ActiveCell.Activate

  ActiveCell.Value = "What?"

End Sub



'---Userform1 module

Private Sub UserForm_Activate()

  With Application

    .SendKeys "{esc}", True

    .GetOpenFilename

    .SendKeys "{esc}", True

  End With

End Sub