英文:
Assign macro to shape, stop automatic updating
问题
在Excel中，我在单元格C6中有一个下拉列表。然后，用户在单元格C10中输入一个数值。接着，我有4个依赖单元格（依赖于单元格C6和输入到单元格C10的值），它们分别位于C13、C17、C18和C19中。其中两个单元格（C17、C18）会在按下形状时根据C10中输入的值更新，然而，另外两个单元格会在单击C10之后自动更新，因为它们使用了公式。由于这是一个内部项目，我无法提供详细的代码...但是否有办法将这些公式添加到宏中，以便在按下按钮时更新所有4个值，而不是目前的方式？或者甚至让这4个值在单击C10之后自动更新（然后我可以删除该形状）。任何帮助将不胜感激。
假设我已经从这些单元格中移除了公式，然后使用以下代码：
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("$C$6,$K$12")) Then
'调用按钮点击子代码（更新C17和C18）
'使用VBA代码更新C13和C19
End If
End Sub
请问您能否编辑这段代码，以便为您提供一个示例...假设我已经为C17和C18编写了VBA代码，如下：
Sub ReadGraphData()
'...
End Sub
并且这些单元格的公式分别为C13：C10*0.001/1.26 和 C19：GetRecentData()（这是另一个模块中的自定义函数）...您要如何编写这部分代码？
英文:
In Excel, I have a dropdown list in cell C6. A user then inputs a numerical value into cell C10. I then have 4 dependent cells (dependent on cells C6, and the value inputted into cell C10), in C13, C17, C18 and C19. 2 of these cells (C17, C18) update according to the inputted value in C10 when a shape is pressed, however, the other 2 cells update automatically when you click off cell C10 because they use formulas instead. I am unable to provide detailed code as it is an internal project... but is there a way to add the formulas to the macro so that all 4 values only update when the button is pressed, rather than how it is now? Or even for all 4 to update when you click off C10 (then I can get rid of the shape). Any help would be appreciated.
Say I have removed the formulas from the cells, then using
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("$C$6,K$12")) Then
'Call button click sub code (update C17 and C18)
'Update C13 and C19 with VBA code
End If
End Sub
Please can you edit this so that you're giving an example... say I have my VBA code for C17 and C18 by
Sub ReadGraphData()
...
End Sub
and the formulas for the cells are given by C13: C10*0.001/1.26 C19: GetRecentData() (this is a personally created function in another module) ... How would I form this?
答案1
得分: 0
请按照以下步骤操作：
-
从单元格 C13 和 C19 中删除公式，以确保它们不再依赖于基于公式的计算。
-
为工作表实施一个“Change”事件。每当工作表发生更改时，该事件将被触发。
只要对单元格 C6 或 C12 进行更改，四个单元格（C13、C17、C18 和 C19）将会自动更新。
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("$C$6,$K$12")) Then
'调用按钮单击子代码（更新C17和C18）
Call ReadGraphData
'使用VBA代码更新C13和C19
[C19] = GetRecentData()
Range("C13").FormulaR1C1 = "=R[-3]C*0.001/1.26"
End If
End Sub
英文:
Please follow these steps:
-
Remove the formulas from cells C13 and C19 to ensure that they no longer rely on formula-based calculations.
-
Implement a Change event for the worksheet. The event will be triggered whenever there is a change made to the worksheet.
All four cells (C13, C17, C18, and C19) will be automatically updated whenever there is a change made to cells C6 or C12.
Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("$C$6,K$12")) Then
'Call button click sub code (update C17 and C18)
Call ReadGraphData
'Update C13 and C19 with VBA code
[C19] = GetRecentData()
Range("C13").FormulaR1C1 = "=R[-3]C*0.001/1.26"
End If
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论