Q:如下圖1所示, 在工作表中, 有3個數值調節按鈕(表單控制項), 每個按鈕連結到一個儲存格。 當點擊數值調節按鈕的上下箭頭時, 連結到的儲存格中的數值會相應的增加或減少。
圖1
通常, 我們按下列步驟連結控制項和儲存格。 首先繪製控制項, 然後選擇控制項並按一下右鍵, 在快顯功能表中選取“設置控制項格式”命令。 在“設置控制項格式”對話方塊的“控制”選項卡中, 設置儲存格連結, 如圖2所示。
圖2
當然, 在圖2所示的“儲存格連結”中, 可以選擇或輸入儲存格區域, 但仍然只是連結到區域左上角的儲存格。 那麼, 如何僅使用一個數值調節按鈕, 將其連結到多個儲存格呢?
下麵是chandoo.org介紹的技巧, 與大家分享。
A:
可以將控制項連結到命名公式, 而該命名公式會基於目前的儲存格返回相應的儲存格, 因此控制項將動態連結到相應的儲存格, 從而實現一個控制項可以改變多個儲存格的值。
如下圖3所示的工作表Sheet2, 我們將這個數值調節按鈕動態連結到儲存格C3、C4、C5, 從而能夠修改這些儲存格中的值。
圖3
定義名稱如圖4所示。
圖4
其中, 名稱:SelectedRow
引用位置:A1
名稱:ControlRange
引用位置:=Sheet2!$C$3:$C$5
名稱:ControlLink
引用位置:=OFFSET(Sheet2!$C$1,SelectedRow-1,0)
設置控制項格式如圖5所示。
圖5
在VBE編輯器的Sheet2代碼模組中, 輸入代碼:
Private SubWorksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target,Range("ControlRange")) Is Nothing Then
Range("SelectedRow").Value =0
Exit Sub
End If
Range("SelectedRow").Value = Target.Row
Application.CalculateFull
End Sub
至此, 所有的工作完成。 選擇儲存格C3, 按一下數值調節按鈕, C3中的數值會相應變化, 對於儲存格C4和C5也是如此, 如圖6所示。
圖6
如果要連結到不連續的儲存格區域, 例如儲存格區域C3:C5、B6:B8、C9:C11中的9個儲存格, 那麼可以將名稱ControlRange修改為:
=Sheet2!$C$3:$C$5,Sheet2!$B$6:$B$8,Sheet2!$C$9:$C$11