0

I need a macro that will renamed a sheet as the user write into an input box. So far i've got this, but when I insert a new name, the old sheet doesnt change its name.

The comparison sheet already exists and is name as "Comparison". I just want to change its name according to a new one that the user will decide.

Sub SalvarAba()

If MsgBox("Keep comparison?", vbQuestion + vbYesNo, "Keep?") = vbYes Then
     s = InputBox("Please enter a sheet name")
     'rename the sheet
End If

End Sub

This may not be the best way to do it, so feel free to change it, if necessary.

Synth
  • 37
  • 5
  • 1
    Refer to that sheet by [codename](https://stackoverflow.com/questions/2649844/excel-tab-sheet-names-vs-visual-basic-sheet-names) and change the `.Name`. Note that you have to verify that user input is in fact a valid name. – BigBen Feb 13 '20 at 17:47
  • 1
    And you will need an inputbox as a message box doesn't allow for any input. – SJR Feb 13 '20 at 17:48

2 Answers2

0
If MsgBox("Keep comparison?", vbQuestion + vbYesNo, "Keep?") = vbYes Then
     s = Inputbox("Please enter a sheet name")
     'rename the sheet
End If
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
0

You can do all this in the form of a inputbox. This takes in the three possible outcomes: The cancel button The OK button with no text the OK button with text I am not a fan of using activeworkbook, but I have no idea what your workbook is named.

I made the changes suggested and added an check for the sheet "Comparison" existing.

    Private Sub test()
Dim wb As Workbook
Dim sht As Worksheet
Dim r As String
Dim result As String
    On Error GoTo None
Set wb = ActiveWorkbook 'can be cnged to ThisWorkbook as needed.

        Set sht = wb.Sheets("Comparison") 'this checks for worksheet by the name of "Comparison"

    r = InputBox("Keep comparison?  Enter name and hit OK, or just cancel to not keep comparison")
    If StrPtr(r) = 0 Then
    ElseIf r = vbNullString Then
    Else
        wb.Sheets("Comparison").Name = r
    End If
None:
End Sub
mooseman
  • 1,997
  • 2
  • 17
  • 29
  • I can't keep pass `code`Dim r As Text`code` "r" is not definited by user. Idk how to proced from here – Synth Feb 13 '20 at 18:19
  • 3
    There's no `Text` type - get rid of `Dim r as Text`. Then, you should refer to the sheet by *codename*, not by name... otherwise this is one-and-done type of macro. The second time you run it, there's no sheet named Comparison (assuming the user actually changed the name the first time). But as is, this won't compile. – BigBen Feb 13 '20 at 18:21
  • 1
    And `wb = ActiveWorkbook` will fail because you need `Set`... but it should probably be `ThisWorkbook` anyway. – BigBen Feb 13 '20 at 18:25
  • @Synth FWIW you want `r` to be declared `As Variant` here. Note the `StrPtr(r)=0` branch handles prompt cancellation; the `r=vbNullString` branch nandles an okayed but empty input, and the `Else` branch handles all other cases (but doesn't validate that `r` is a valid sheet name, so errors might happen depending on the user's input and its length). – Mathieu Guindon Feb 13 '20 at 18:42
  • @Mathieu Guindon thank you! I noticed that, if there is no "Comparison" sheet, this will run an error too. Is there a way to get rid of it? – Synth Feb 13 '20 at 18:53
  • 1
    @Synth try and see if you can find a question on this site to "determine if a worksheet exists", like [here](https://stackoverflow.com/a/6688482/1188513) – Mathieu Guindon Feb 13 '20 at 18:55
  • @BigBen "The comparison sheet already exists and is name as "Comparison". " I believe his question mentioned nothing about running it a second time or that there might not be the required worksheet. I made the necessary changes. Also, not having the workbook, I can't refer to it by the codename. – mooseman Feb 20 '20 at 18:14