Unprotected Excel Sheet Runtime 1004 error – 无法设置范围的数字格式

huangapple go评论47阅读模式
英文:

Unprotected Excel Sheet Runtime 1004 error - can't set numberformat of Range

问题

I have a simple sub running on togglebutton click as part of a larger vba project. Neither the workbook or sheet are protected. I want to set the format of an output cell (E18 on the same sheet as the button) to match the units the formula is calculated in. The errors occur on the commented-out lines. Without the format lines the sub and the larger project both work perfectly fine.

Private Sub ToggleButtonLB_Click()

If ToggleButtonLB.Value = True Then
    ToggleButtonLB.Caption = "kg"
    'Range("E18").NumberFormat = "0.0 kg"
    
    Else
    ToggleButtonLB.Caption = "lb"
    'Range("E18").NumberFormat = "0.0 lb"
End If
End Sub

Any help would be greatly appreciated!

英文:

I have a simple sub running on togglebutton click as part of a larger vba project. Neither the workbook or sheet are protected. I want to set the format of an output cell (E18 on the same sheet as the button) to match the units the formula is calculated in. The errors occur on the commented-out lines. Without the format lines the sub and the larger project both work perfectly fine.

Private Sub ToggleButtonLB_Click()

If ToggleButtonLB.Value = True Then
    ToggleButtonLB.Caption = "kg"
    'Range("E18").NumberFormat = "0.0 kg"
    
    Else
    ToggleButtonLB.Caption = "lb"
    'Range("E18").NumberFormat = "0.0 lb"
End If
End Sub

Any help would be greatly appreciated!

答案1

得分: 1

Here is the translated code portion:

如果你运行宏录制器并更改单元格为所需格式,你会得到:

Selection.NumberFormat = "0.0 ""kg"""

因此,在你的代码中,请尝试:

Private Sub ToggleButtonLB_Click()

If ToggleButtonLB.Value = True Then
    ToggleButtonLB.Caption = "kg"
    Range("E18").NumberFormat = "0.0 ""kg"""
    
    Else
    ToggleButtonLB.Caption = "lb"
    Range("E18").NumberFormat = "0.0 ""lb"""
End If
End Sub
英文:

If you run the macro recorder and change the cell to your desired format, you will get:

Selection.NumberFormat = "0.0 ""kg"""

So in your code, try:

Private Sub ToggleButtonLB_Click()

If ToggleButtonLB.Value = True Then
    ToggleButtonLB.Caption = "kg"
    Range("E18").NumberFormat = "0.0 ""kg"""
    
    Else
    ToggleButtonLB.Caption = "lb"
    Range("E18").NumberFormat = "0.0 ""lb"""
End If
End Sub

huangapple
  • 本文由 发表于 2023年6月5日 04:52:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/76402373.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定