重命名工作表基于INPUT BOX
请帮我改变下面的代码。需要通过InputBox
重命名工作表基于INPUT BOX
由于选择重命名小区
Sub RenWSs()
Dim WS As Worksheet
Dim shtName
Dim newName As String
Dim i As Integer
For Each WS In Worksheets
With WS
If Trim(.Range("c14")) <> "" Then
shtName = Split(Trim(.Range("c14")), " ")
newName = shtName(0)
On Error GoTo ws_name_error
.Name = newName
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
有可以使用InputBox
几种方式接入小区C4。
一种是通过在InputBox
选择String
,请参见下面的代码:
Dim RngStr As String
RngStr = Application.InputBox(prompt:="Select the Cell for the new Sheet's name", Type:=2)
If Trim(.Range(RngStr)) <> "" Then
另一个,是通过在InputBox
选择Range
,请参见下面的代码:
Dim rng As Range
Set rng = Application.InputBox(prompt:="Select the Cell for the new Sheet's name", Type:=8)
If Trim(rng) <> "" Then
全球代码
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 = newName
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
要了解更多有关InputBox
功能:您要使用`InputBox`什么https://msdn.microsoft.com/en-us/library/office/ff839468.aspx
非常感谢您的帮助。我认为第一个是好的,因为我没有按范围选择名称,我打电话给特定的单元格进行重命名(新名称已经在主题单元格中可用)。你能告诉我在哪里我必须粘贴到你的代码 –
@ayyappankm阅读完整的代码(刚刚添加) –
谢谢,但输入框正在为每个工作表要求新名称。我需要输入一次来改变所有的工作表,请帮助 –
?输入单元格的地址,如“C4”?或者输入当前电路图的新名称? –
进入单元格“C4”单元格 –
请参阅我的回答以下两种使用“InputBox”选择单元格的方法 –