AJS
09-15-2004, 12:07 AM
Hi,
I have been working on a set of macros for about a week (newbie), and they have suddenly started returning the following error message in an error box:
400
I have no idea what this means. I initially thought that my spreadsheet was corrupted, but after copying and pasting the modules the error messages continue to pop up. I have also tried reinstalling office. Any ideas?
The offending code is below. It basically clears all sheets in a workbook from sheet2 down, bar a few critical cells in each:
Sub ResetForm()
Dim rBig As Range
Dim rSmall As Range
Dim rSmall1 As Range
Dim rSmall2 As Range
Dim Cell As Range
Dim rNew As Range
' Clear all worksheets for the next set of measurements
Sheet1.Protect UserInterfaceOnly:=True
ans = MsgBox("Do you want to proceed and reset all of the dimensional measurements?", vbOKCancel)
If ans = vbCancel Then Exit Sub
Application.ScreenUpdating = False
Sheet1.Range("C7") = Worksheets.Count - 1
Sheet1.Range("D6") = 1
' Remove the charts
For i = 1 To Sheet1.Range("C7")
Sheets(i + 1).Select
If ActiveSheet.ChartObjects.Count > 0 Then
ActiveSheet.ChartObjects.Delete
End If
' Select all but the ranges A1:B3, A5:C5
Set rBig = ActiveSheet.UsedRange
Set rSmall1 = Range("A1:B3")
Set rSmall2 = Range("A5:C5")
Set rSmall = Union(rSmall1, rSmall2)
Set rNew = Nothing
For Each Cell In rBig
If Intersect(Cell, rSmall) Is Nothing Then
If rNew Is Nothing Then
Set rNew = Cell
Else
Set rNew = Union(rNew, Cell)
End If
End If
Next Cell
rNew.Select
' Clear the selected ranges
Selection.ClearContents
Selection.ClearFormats
Selection.HorizontalAlignment = xlCenter
Range("A6").Select
ActiveSheet.UsedRange
Next i
' Default back to Measurement sheet
Sheet1.Select
Range("D6").Select
ActiveSheet.UsedRange
Application.ScreenUpdating = True
End Sub
I have been working on a set of macros for about a week (newbie), and they have suddenly started returning the following error message in an error box:
400
I have no idea what this means. I initially thought that my spreadsheet was corrupted, but after copying and pasting the modules the error messages continue to pop up. I have also tried reinstalling office. Any ideas?
The offending code is below. It basically clears all sheets in a workbook from sheet2 down, bar a few critical cells in each:
Sub ResetForm()
Dim rBig As Range
Dim rSmall As Range
Dim rSmall1 As Range
Dim rSmall2 As Range
Dim Cell As Range
Dim rNew As Range
' Clear all worksheets for the next set of measurements
Sheet1.Protect UserInterfaceOnly:=True
ans = MsgBox("Do you want to proceed and reset all of the dimensional measurements?", vbOKCancel)
If ans = vbCancel Then Exit Sub
Application.ScreenUpdating = False
Sheet1.Range("C7") = Worksheets.Count - 1
Sheet1.Range("D6") = 1
' Remove the charts
For i = 1 To Sheet1.Range("C7")
Sheets(i + 1).Select
If ActiveSheet.ChartObjects.Count > 0 Then
ActiveSheet.ChartObjects.Delete
End If
' Select all but the ranges A1:B3, A5:C5
Set rBig = ActiveSheet.UsedRange
Set rSmall1 = Range("A1:B3")
Set rSmall2 = Range("A5:C5")
Set rSmall = Union(rSmall1, rSmall2)
Set rNew = Nothing
For Each Cell In rBig
If Intersect(Cell, rSmall) Is Nothing Then
If rNew Is Nothing Then
Set rNew = Cell
Else
Set rNew = Union(rNew, Cell)
End If
End If
Next Cell
rNew.Select
' Clear the selected ranges
Selection.ClearContents
Selection.ClearFormats
Selection.HorizontalAlignment = xlCenter
Range("A6").Select
ActiveSheet.UsedRange
Next i
' Default back to Measurement sheet
Sheet1.Select
Range("D6").Select
ActiveSheet.UsedRange
Application.ScreenUpdating = True
End Sub