Prompting to Rename Worksheet

[Mark_G]

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

 

[dtaylor]

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

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]

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]

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]

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

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