New to VBA - Object variable or With block variable not set

stvnx7

New Member
Joined
Apr 10, 2013
Messages
3
Hi Everyone,

I am new to VBA, and I figured that trying to code is the best way to code. Anyway, I am trying to code a macro that will delete all worksheets in a workbook, unless that worksheet is named "Sheet1". In which case, I want the user to input what "Sheet1" should be named.

I am getting the run-time error "Object variable or With block variable not set". Here is the current code:

Code:
Sub RanameSheet1()
'
' RanameSheet1 Macro
' Deletes ALL worksheets except Sheet1. Renames Sheet1 to user input.
'
' Keyboard Shortcut: Ctrl+Shift+H
'
    Dim myWorksheet As Worksheet
    For Each myWorksheet In Worksheets
    If myWorksheet.Name <> "Sheet1" Then
        myWorksheet.Delete
    End If
    Next
    If myWorksheet.Name = "Sheet1" Then
    myInput = InputBox("Rename Worksheet 1 to:")
        myWorksheet.Name = myInput
    End If
    ActiveWorkbook.Save
    Run ("Workbook Saved")
End Sub

Can anyone tell me what I am doing wrong here? The example on MSDN website didn't use "Set" when defining a variable. Thanks for the help everyone!

-Steven
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Hi stvnx7, welcome to MrExcel Forum and Message Board.
The easy way around it is to move your second If...Then statement to inside the For...Next loop.

p.s. A little explanation. The For Each method automatically determines the object variable while the loop is processing, but once the loop completes, the object variable reverts to Nothing. That means that to continue using the same code as an object variable you would have to define it with a Set statement. So by moving the second If...Then statement to inside the loop, it is automatically defined and that If...Then statement will only be true once, if at all.
 
Last edited:

stvnx7

New Member
Joined
Apr 10, 2013
Messages
3
Thanks JLGWhiz.

I tried that two different ways and they both resulted in different runtime errors:

Code:
Sub RanameSheet1()
'
' RanameSheet1 Macro
' Deletes ALL worksheets except Sheet1. Renames Sheet1 to user input.
'
' Keyboard Shortcut: Ctrl+Shift+H
'
    Dim myWorksheet As Worksheet
    For Each myWorksheet In Worksheets
    If myWorksheet.Name <> "Sheet1" Then
        myWorksheet.Delete
    End If
    If myWorksheet.Name = "Sheet1" Then
    myInput = InputBox("Rename Sheet1 to:")
        myWorksheet.Name = myInput
    End If
    Next
End Sub

As well as:

Code:
Sub RenameSheet1()
'
' RanameSheet1 Macro
' Deletes ALL worksheets except Sheet1. Renames Sheet1 to user input.
'
' Keyboard Shortcut: Ctrl+Shift+H
'
    Dim myWorksheet As Worksheet
    For Each myWorksheet In Worksheets
    If myWorksheet.Name <> "Sheet1" Then
        myWorksheet.Delete
    If myWorksheet.Name = "Sheet1" Then
        myInput = InputBox("Rename Sheet1 to:")
        myWorksheet.Name = myInput
    End If
    End If
    Next
End Sub

:-/
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Excel requires that you keep at least one worksheet in a workbook, so I am assuming that you want to keep sheet1, but remame it after all the other sheets are deleted.
Code:
Sub RanameSheet1() '
' RanameSheet1 Macro
' Deletes ALL worksheets except Sheet1. Renames Sheet1 to user input.'
' Keyboard Shortcut: Ctrl+Shift+H
'
    Dim myWorksheet As Worksheet
    For Each myWorksheet In Worksheets
    If myWorksheet.Name <> "Sheet1" Then
        myWorksheet.Delete
    End If
    Next
    Sheets("Sheet1").Name = InputBox("Rename Worksheet 1 to:")
    ActiveWorkbook.Save
    Run ("Workbook Saved")
End Sub
 

stvnx7

New Member
Joined
Apr 10, 2013
Messages
3
JLGWhiz,

Thank you very much for your WORKING code!

I actually resorted to the following code, but I didn't like it because it used the "For Each" loop, which I thought was unnecessary since at that point there would only be one worksheet, and therefore it didn't seem right to use the loop.

Code:
Sub RenameSheet1Wrong()
Dim myWorksheet As Worksheet
For Each myWorksheet In Worksheets
If myWorksheet.Name <> "Sheet1" Then
    myWorksheet.Delete
End If
Next
Dim myWorksheetAgain As Worksheet
Dim myInput As String
myInput = InputBox("Rename Sheet1 to:")
For Each myWorksheetAgain In Worksheets
If myWorksheetAgain.Name = "Sheet1" Then
    myWorksheetAgain.Name = myInput
End If
Next
End Sub

Your code is much cleaner, and I am going to go with that one.

My other solution was going to be to use the ActiveSheet and use

Code:
Activesheet.Name = InputBox("Rename Active Worksheet to:")

All of these are examples that would work. My second "For Each" solution seems like a misuse of that loop, and my "ActiveSheet.Name" solution would rename the active worksheet, not necessarily "Sheet1", although after running the "For Each" loop the active worksheet should be "Sheet1", I feel that your solution is more robust.

Thanks!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
JLGWhiz,

Thank you very much for your WORKING code!

I actually resorted to the following code, but I didn't like it because it used the "For Each" loop, which I thought was unnecessary since at that point there would only be one worksheet, and therefore it didn't seem right to use the loop.

Code:
Sub RenameSheet1Wrong()
Dim myWorksheet As Worksheet
For Each myWorksheet In Worksheets
If myWorksheet.Name <> "Sheet1" Then
    myWorksheet.Delete
End If
Next
Dim myWorksheetAgain As Worksheet
Dim myInput As String
myInput = InputBox("Rename Sheet1 to:")
For Each myWorksheetAgain In Worksheets
If myWorksheetAgain.Name = "Sheet1" Then
    myWorksheetAgain.Name = myInput
End If
Next
End Sub

Your code is much cleaner, and I am going to go with that one.

My other solution was going to be to use the ActiveSheet and use

Code:
Activesheet.Name = InputBox("Rename Active Worksheet to:")

All of these are examples that would work. My second "For Each" solution seems like a misuse of that loop, and my "ActiveSheet.Name" solution would rename the active worksheet, not necessarily "Sheet1", although after running the "For Each" loop the active worksheet should be "Sheet1", I feel that your solution is more robust.

Thanks!

Glad you could use it.
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top