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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,167
Messages
5,570,640
Members
412,334
Latest member
ExcelForLifeDontHate
Top