Lost userform in Excel 2010

[philrock]

I created a userform in a Windows 7 (64 bit) / Excel 2010 spreadsheet I use for engineering calculations. The userform worked as intended. I then copied the spreadsheet to an XP computer running Excel 97 and edited the code behind the userform. The edited userform ran fine on XP / Excel 97. I then copied the spreadsheet back to the Win 7 computer, intending to do some more editing on the userform. The userform seems to have disappeared completely from the code editor.

Any ideas on what may have happened here?

Thanks,
philrock

 

[remeng]

Did you put the form in the global or file code section?

 

[philrock]

remeng,

I'm a newbie at this - I don't know the terminology in your question. Below are images of the VBA Project tree. The first image is from the XP computer, where everything works fine. The second image is from the Win 7 computer where I have opened the spreadsheet that I had saved on the XP computer.

 

[SkipVought]

Hi,

Was you workbook saved as an .xlsx, rather than and .xlsm or .xlsb?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[philrock]

SkipVought,

I first created the workbook in Win 7 with Excel 2010 in Compatibility mode, and saved it as an xls file.

Since my most recent post, I found that if I save the Userform (from XP / 97) separately as an frm file, then save the workbook, I can then open the saved workbook in Excel 2010, then import the frm file and things are back to working o.k. again. This is certainly a manageable workaround, but it would be nice not to have to do it.

Should I be using xlsx files?

 

[SkipVought]

Xlsx files have NO VBA MACROS!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[remeng]

It looks like your macro is save to the file and not globally. Could you please add your code?

Mike

 

[philrock]

The name of the workbook is Springs-Compr-Dialog-01.xls.

The title bar in the code window reads: Springs-Compr-Dialog-01.xls - Userform1(Code)
Below is the code in the code window:
[tt]
Private Sub CommandButton1_Click()
Dim _
Dout, _
Doutmin, _
Doutmax, _
DW, _
DWmin, _
DWmax, _
DI, _
DImin, _
DImax _
As Double
Dim Row As Long

'reset indicator column to all 0s:
For Row = 25 To 30
Cells(Row, 44) = 0
Next Row

'Gather inputs:
Doutmin = CDbl(TBODMIN.Text)
Doutmax = CDbl(TBODMAX.Text)
DWmin = CDbl(TBWDMIN.Text)
DWmax = CDbl(TBWDMAX.Text)
DImin = CDbl(TBIDMIN.Text)
DImax = CDbl(TBIDMAX.Text)

'Row checking loop:
For Row = 25 To 30
Dout = Cells(Row, 3) 'outside dia
DW = Cells(Row, 5) 'wire dia
DI = Cells(Row, 6) 'inside dia
If _
Doutmin <= Dout And _
Dout <= Doutmax And _
DWmin <= DW And _
DW <= DWmax And _
DImin <= DI And _
DI <= DImax _
Then Cells(Row, 44) = 1
If Cells(Row, 44) = 0 Then Cells(Row, 44) = "NG"
Next Row
End Sub
[/tt]

(For some reason, the Tek-Tips editor did not preserve my indenting.)

 

[strongm]

Just as an aside

Dim _
Dout, _
Doutmin, _
Doutmax, _
DW, _
DWmin, _
DWmax, _
DI, _
DImin, _
DImax _
As Double
Dim Row As Long


isn't doing what you think it is ... Dout, Doutmin, Doutmax, DW, DWmin, DWmax, DI, DImin are all going to be Variants, not Doubles. VBA requires explicit dimensioning for each variable.

 

[remeng]

Thank you for the code. I have some questions:

1) Why make the edits in 97 in the first place when you can do all the edits in 2010 and save it in the new format?
2) Have you tried opening the code in 2010 and just pasting the old code into the new VB file? You can export your form from 97 by right clicking on the for icon and clicking export. Save it and then in 2010 VB just import it.
3) If you are intending to make changes to the original doc, why not use the original doc?

I'm not quite understanding why you are downgrading and then upgrading the software versions. Is there a reason for going to 97?

Mike

 

[philrock]

strongm,

Thanks for pointing out my declaration error. Man - do I have a lot of code to fix.


Mike,

1) The 2010 computer is at the office. The 97 computer is at home. If I have a personal energy surge or inspiration at home in the evening, I like to be able to do a little work there. Upgrading the home computer to Win 7 is possible, but scary.

2) I successfully did something similar to your suggestion, as described in my post above from 21 Oct 13 22:39. I was hoping to avoid the extra steps.

Phil

 

[remeng]

If you have XP on the home computer, why not just update office from 97 to 2010. You wont need to change the OS.

 

[philrock]

MIke,

Excellent idea. I had been assuming Office 2010 requires Windows 7, which, as you point out, is not the case.

Thanks!