使用下拉列表重命名工作表 [英] Rename Worksheet with Dropdown list

问题描述

我正在使用以下代码重命名工作表.

I am using the below code to rename worksheet.

Option Explicit

Sub RenWSs()

    Dim ws As Worksheet
    Dim shtName
    Dim newName As String
    Dim i As Integer
    Dim RngStr  As String

    RngStr = Application.InputBox(prompt:="Select the Range for the new Sheet's name", Type:=2)
    For Each ws In Worksheets
        With ws
            If Trim(.Range(RngStr)) <> "" Then
                shtName = Split(Trim(.Range(RngStr)), " ")
                newName = shtName(0)
                On Error GoTo ws_name_error
                .Name = .Range(RngStr)
                GoTo done
repeat:
                .Name = newName & i
                GoTo done
ws_name_error:
                i = i + 1
                Resume repeat
            End If
        End With
        On Error GoTo 0
done:
    Next

End Sub

在这种情况下，我要通过输入框选择新名称，并且可以正常使用.现在我想要的是，在调用输入框之前，必须完成以下过程.

In this i am selecting the new name through Input Box and its working fine. Now what i want is, before calling the input box, the below process has to be done.

我在下拉列表中有名称，下拉列表中的每个名称都将在所有工作表(如J16)中逐一更新.

I have names in drop down list, each names in drop down list to be updated one by one in all worksheets like J16 is the cell.

请帮助我

推荐答案

下面的代码将遍历所有 ws 工作表，并将"J16"中的单元格值修改为测试1"(只是出于测试目的.

The code below will lopp through all ws sheets, and modifies the value of cell in "J16" to "Test 1" (just for testing purposes).

Option Explicit


Sub ModifyDropDownValue()

Dim ws As Worksheet

For Each ws In Worksheets
    With ws
        ' modify the value in cell J16
        .Range("J16").Value = "Test 1"
    End With
Next ws

End Sub

这篇关于使用下拉列表重命名工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！