Oh. My. Code. Excel VBA Needs

September 8, 2006 6:14 PM

I've created a button in Excel 2002 that runs some VBA when clicked. Works great. I've emailed the spreadsheet home and opened it with Excel 97 and clicking does nothing. When I run the subroutine from VBA editor, it works fine. So, what gives? Why won't this work in Excel 97 and what can I change so that it will run in Excel 97 and beyond? I've pasted the subroutine into the Extended box. Oh, and I'm really an infant with this, so please try and communicate on that level.

Private Sub CommandButton1_Click()
Dim wsSheet As Worksheet
On Error Resume Next
Set wsSheet = Sheets(ComboBox1.Value & ComboBox2.Value)
If Not wsSheet Is Nothing Then
MsgBox ("The month you have selected already exists as a worksheet name. Please choose another month and try again.")
Exit Sub
Else
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets("Template")
Sheets("Template (2)").Name = ComboBox1.Value & ComboBox2.Value
Sheets(ComboBox1.Value & ComboBox2.Value).Select
Sheets(ComboBox1.Value & ComboBox2.Value).Unprotect
Sheets(ComboBox1.Value & ComboBox2.Value).Shapes("ComboBox1").Delete
Sheets(ComboBox1.Value & ComboBox2.Value).Shapes("ComboBox2").Delete
Sheets(ComboBox1.Value & ComboBox2.Value).Shapes("CommandButton1").Delete
Sheets(ComboBox1.Value & ComboBox2.Value).Columns("J:N").Delete Shift:=xlToLeft
Sheets(ComboBox1.Value & ComboBox2.Value).Range("B2").FormulaR1C1 = "'" & ComboBox1.Value & " " & ComboBox2.Value
'Sheets(ComboBox1.Value & ComboBox2.Value).Tab.ColorIndex = -4142
Sheets(ComboBox1.Value & ComboBox2.Value).Protect
Exit Sub
End If
End Sub

posted by kc0dxh to Computers & Internet (11 answers total)

This might be a stab in the dark but might this have something to do with your security settings in Excel 97? Like it's set at a high security level that isn't properly running this code? Just a guess...
posted by BlzOfGlry at 6:21 PM on September 8, 2006

Response by poster: Excel 97 doesn't have security settings beyond on and off. It can also be set to prompt upon opening each workbook, which is how i have mine set.
posted by kc0dxh at 8:22 PM on September 8, 2006

If this were my problem, I'd try actually doing the whole thing again using Excel 97, rather than just expecting a workbook created using 2002 to work in 97.

You've got a far better chance of stuff done in 97 working in 2002 than the other way around. MS are really good at backward compatibility; forward compatibility, not so much.
posted by flabdablet at 8:43 PM on September 8, 2006

Take out the On Error Resume Next so you can see what, if any, errors you're getting.
posted by evariste at 8:56 PM on September 8, 2006

Response by poster: I've commented out the On Error Resume Next. When I click the button it errors out with: Run-time error: 9, Subscript out of range.

When I click Debug, the line indicated is:
Set wsSheet = Sheets(ComboBox1.Value & ComboBox2.Value)
posted by kc0dxh at 9:04 PM on September 8, 2006

If you comment out the On Error Resume Next then, if the worksheet already exists, you should expect that error.

I don't know specifically why it's not running. I do notice though that you appear to be using controls (ComboBox1 etc.) after you've deleted them. You probably intend to be using the controls from the Template sheet but you aren't because you've selected your new sheet. I suspect that XL2000 and greater is more tolerant of this sort of thing so you're getting away with it there - but not in XL97.

I've tweaked your code below to make it a little tidier and hopefully it should work. If it doesn't work then

Private Sub CommandButton1_Click()
Dim wsSheet As Worksheet
Dim shNew As Worksheet
Dim mySheetName As String

' explicitly state you're taking these values
' from the comboboxes on this sheet
mySheetName = Me.ComboBox1.Value & Me.ComboBox2.Value

On Error Resume Next
Set wsSheet = ThisWorkbook.Sheets(mySheetName)
If Not wsSheet Is Nothing Then
MsgBox ("The month you have selected already exists as a worksheet name: " _
& mySheetName & vbCrLf & vbCrLf _
& "Please choose another month and try again.")
Set wsSheet = Nothing
Exit Sub
Else

On Error GoTo 0

'copy w/o relying on names
Dim i As Integer
For i = 1 To ThisWorkbook.Sheets.Count
If ThisWorkbook.Sheets(i) Is Me Then Exit For
Next i

Me.Copy After:=Me
Set shNew = ThisWorkbook.Sheets(i + 1)
With shNew
.Name = mySheetName
.Unprotect
.Shapes("ComboBox1").Delete
.Shapes("ComboBox2").Delete
.Shapes("CommandButton1").Delete
'alternate way of deleting ALL controls:
'Dim shp As Shape
'For Each shp In .Shapes
' If shp.Type = msoFormControl Then shp.Delete
'Next shp

.Columns("J:N").Delete Shift:=xlToLeft
.Range("B2").FormulaR1C1 = "'" & ComboBox1.Value & " " & ComboBox2.Value
.Protect
End With
Set shNew = Nothing 'tidy up

End If

End Sub

If it doesn't work post back with any error msg etc.
posted by NailsTheCat at 10:59 AM on September 9, 2006

Response by poster: NailsTheCat:

For i = 1 to 1000
MsgBox("Thank you!")
Next i

I'm out of time today. I'll test this Sunday probably.
posted by kc0dxh at 3:18 PM on September 9, 2006

You're welcome, good luck.

Just noticed:

.Range("B2").FormulaR1C1 = "'" & ComboBox1.Value & " " & ComboBox2.Value
should read

.Range("B2").FormulaR1C1 = "'" & me.ComboBox1.Value & " " & me.ComboBox2.Value

- again, to make sure you use the controls of the Template sheet.
HTH
posted by NailsTheCat at 4:36 AM on September 10, 2006

Response by poster: Well, NailTheCat, I've put your code into the worksheet. Excel crashes when compliling the code. I'm trying retyping it in case copying from HTML is incorporating something that isn't visible in the VBA editor.
posted by kc0dxh at 8:16 AM on September 10, 2006

Response by poster: I've retyped it from scrath and it still crashes when compiled. There's something in there that Excel97 just doesn't like, but there's very little in the dump code (just a memory address, I think) and there's no help from Microsoft other than "Upgrade".

If you, or anyone, can help I'd love it, but don't worry too much. I've mangled something together in a macro that seems to work well enough. Funny that I can't just take the macro code and drop it into a Private Sub. I thought they'd be the same thing.
posted by kc0dxh at 10:39 AM on September 10, 2006

Sorry - been travelling... Hmmm. That sounds crazy that it crashes on compile. Stupid question: are you putting it in a new workbook? If not you should try that first. Excel (in particular the early versions) that can easily corrupt.

Otherwise... I'm wondering whether Excel 97 likes 'me'. You could try compiling with those lines commented out and then if that solves the crash then it's a quick rewrite.
posted by NailsTheCat at 10:58 AM on September 12, 2006