Prompting to Rename Worksheet

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
Hi,

I currently have the following as part of one oy my macros

Sheets("Results&Comments (2)").Select
ActiveSheet.Name = Range("$A$2").Value


What I want to do is make it an if statement that will prompt the user to enter a new worksheet name if the worksheet already exists. With the code as I have it now, if the sheet called "stratum1" exists, and the value in cell A2 is "stratum1" the code errors. Is there a way to allow the user to enter a new worksheet name and then continue on with the code.

Mark
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

dtaylor

Active Member
Joined
Mar 21, 2002
Messages
379
Hi Mark -

Here is something to get you started - you will need to adjust for your code but I tested it and it worked for me.
In a public module this code will prompt the user with an imputbox to rename the activesheet if the activesheet name matches range A2 - else will name the sheet range A2 value

Code:
Dim NewName As String

For Each wk In ActiveWorkbook.Sheets

If wk.Name = ActiveSheet.Range("$A$2").Value Then
NewName = InputBox("WorkSheet Exists - Enter New Name")
ActiveSheet.Name = NewName
GoTo fin
End If

Next wk

ActiveSheet.Name = Range("$A$2").Value
fin:

hope this helps you along!

Dan
 

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
Ok, Now that I think about it, renaming the new sheet is not really what I want to do.

What I want to do is rename the existing sheet and then allow that new sheet that the code is creating to be called the value of A2.

Is that possible? If that is not possible, then I guess the best way is that if a worksheet exists with the same name as the value of A2, I want a msg box to appear telling the user that the sheet already exists and telling them to manually rename the sheet. If the message box appears, the macro will justg go to the end of the code.

Mark
 

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
Ok, I managed to figure out a way to do what I was looking for. I used the following code.

Sheets("Results&Comments").Range("A501:A600").ClearContents

Dim I As Integer

For I = 1 To Sheets.Count
Sheets("Results&Comments").Cells(65530, 1).End(xlUp).Offset(1, 0) = Sheets(I).Name
Next I

If Sheets("Results&Comments").Range("B501") = Sheets("Results&Comments").Range("A2") Then MsgBox "You have already compiled this stratum. Please go back and either rename or delete the worksheet that exists for this stratum"
If Sheets("Results&Comments").Range("B501") = Sheets("Results&Comments").Range("A2") Then GoTo Line1

If Sheets("Results&Comments").Range("B502") = Sheets("Results&Comments").Range("A2") Then MsgBox "You have already compiled this stratum. Please go back and either rename or delete the worksheet that exists for this stratum"
If Sheets("Results&Comments").Range("B502") = Sheets("Results&Comments").Range("A2") Then GoTo Line1

If Sheets("Results&Comments").Range("B503") = Sheets("Results&Comments").Range("A2") Then MsgBox "You have already compiled this stratum. Please go back and either rename or delete the worksheet that exists for this stratum"
If Sheets("Results&Comments").Range("B503") = Sheets("Results&Comments").Range("A2") Then GoTo Line1

If Sheets("Results&Comments").Range("B504") = Sheets("Results&Comments").Range("A2") Then MsgBox "You have already compiled this stratum. Please go back and either rename or delete the worksheet that exists for this stratum"
If Sheets("Results&Comments").Range("B504") = Sheets("Results&Comments").Range("A2") Then GoTo Line1

If Sheets("Results&Comments").Range("B505") = Sheets("Results&Comments").Range("A2") Then MsgBox "You have already compiled this stratum. Please go back and either rename or delete the worksheet that exists for this stratum"
If Sheets("Results&Comments").Range("B505") = Sheets("Results&Comments").Range("A2") Then GoTo Line1

If Sheets("Results&Comments").Range("B506") = Sheets("Results&Comments").Range("A2") Then MsgBox "You have already compiled this stratum. Please go back and either rename or delete the worksheet that exists for this stratum"
If Sheets("Results&Comments").Range("B506") = Sheets("Results&Comments").Range("A2") Then GoTo Line1

If Sheets("Results&Comments").Range("B507") = Sheets("Results&Comments").Range("A2") Then MsgBox "You have already compiled this stratum. Please go back and either rename or delete the worksheet that exists for this stratum"
If Sheets("Results&Comments").Range("B507") = Sheets("Results&Comments").Range("A2") Then GoTo Line1

If Sheets("Results&Comments").Range("B508") = Sheets("Results&Comments").Range("A2") Then MsgBox "You have already compiled this stratum. Please go back and either rename or delete the worksheet that exists for this stratum"
If Sheets("Results&Comments").Range("B508") = Sheets("Results&Comments").Range("A2") Then GoTo Line1

If Sheets("Results&Comments").Range("B509") = Sheets("Results&Comments").Range("A2") Then MsgBox "You have already compiled this stratum. Please go back and either rename or delete the worksheet that exists for this stratum"
If Sheets("Results&Comments").Range("B509") = Sheets("Results&Comments").Range("A2") Then GoTo Line1

If Sheets("Results&Comments").Range("B510") = Sheets("Results&Comments").Range("A2") Then MsgBox "You have already compiled this stratum. Please go back and either rename or delete the worksheet that exists for this stratum"
If Sheets("Results&Comments").Range("B510") = Sheets("Results&Comments").Range("A2") Then GoTo Line1

'This checks to make sure that you have entered stratum information, this message will appear if no well spaced info has been entered for the stratum

If Sheets("Formulas and Tables").Range("EQ1256") = 0 Then MsgBox "You have not entered enough information to compile this stratum, go back and enter more information"
If Sheets("Formulas and Tables").Range("EQ1256") = 0 Then GoTo Line1


The first part puts all the sheet names in one column. And then I have a formula that pulls out only those sheets that are called "stratum 1", stratum 2, and so on up to stratum 10. THe macro then looks to see if the value in a2 is already in the stratum list. If it is, the message box pops up and ends the macro.

Mark
 

dtaylor

Active Member
Joined
Mar 21, 2002
Messages
379
Hi Mark -

Instead of hardcoding each cell in range b501:b601 just activate another loop for that range - something like this - would be a lot less typing

Code:
For i = 501 To 600

If Sheets("Results&Comments").Range("B" & i) = Sheets("Results&Comments").Range("A2") Then
MsgBox "You have already compiled this stratum. Please go back and either rename or delete the worksheet that exists for this stratum"
GoTo Line1
End If

Next i

Line1:


Regards
Dan
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,546
Messages
5,838,021
Members
430,526
Latest member
NiceGuyWithExcel2007

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