根据字符串长度自动调整CommandButton的大小

Question

我有几个带有文本的命令按钮，其标题会根据各种输入而更改。还要提到的是，这些是工作表本身的命令按钮，而不是表单上的命令按钮

有没有一种方法可以自动调整大小，使按钮宽度适应文本的长度？

以下是相关代码的片段：

Sub FixButtonWidth()
'Create Button
Set rbtn = ActiveSheet.Buttons.Add(0, 0, 30, 20)
'User enters a string of some length
ShowThis = InputBox("What do you want to call your button?", "Button Name", "")
'Button Caption changes to whatever the user input
rbtn.Caption = ShowThis
'This next line is the problem:
'The width needs to be based on the width of the string
'Len(ShowThis) only gives the number of characters in the string . . .
'Since letters like 'Z' are much wider than letters ...
'like i (for example), Len(ShowThis) is not a good solution.
rbtn.Width = 500
End Sub

Answer 1

就像我说的在set rbtn之后使用rbtn.AutoSize = True。。。部分并移除rbtn.width = 600

Sub FixButtonWidth()
'Create Button
Set rbtn = ActiveSheet.Buttons.Add(0, 0, 30, 20)
'User enters a string of some length
ShowThis = InputBox("What do you want to call your button?", "Button Name", "")
rbtn.AutoSize = True
'Button Caption changes to whatever the user input
rbtn.Caption = ShowThis
'This next line is the problem:
'The width needs to be based on the width of the string
'Len(ShowThis) only gives the number of characters in the string . . .
'Since letters like 'Z' are much wider than letters like i, for example Len(ShowThis) is not the solution
'rbtn.Width = 500
End Sub