vba Excel VBA滚动条向左或向右移动工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12940348/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Scroll bar which shift sheet left or right
提问by moshjosh
Request: VBA code for mini scroll bar which shift sheet left or right using VBA or ActiveX scroll bar?
请求：使用 VBA 或 ActiveX 滚动条向左或向右移动工作表的迷你滚动条的 VBA 代码？
I have created a trainer skills matrix which has trainer names listed down the side (in cells 'B7' through 'B86') and Skill disciplines listed along the top (in cells E6 through 'AJ6'). Where the trainers name and a skill intersect I have a dropdown list stating ‘Y' for has skill, ‘N' does not skill and ‘n\a'. if the skill is not appropriate for that trainer. I have frozen the header rows (in cells E6 to 'AJ6') and trainer names (in cells 'B7' through 'B86') for easy cross reference
我创建了一个培训师技能矩阵，其中列出了培训师姓名（在单元格“B7”到“B86”中）和顶部列出的技能学科（在单元格 E6 到“AJ6”中）。在培训师姓名和技能相交的地方，我有一个下拉列表，上面写着“Y”表示有技能，“N”不是技能，“n\a”。如果该技能不适合该培训师。我已经冻结了标题行（在单元格 E6 到“AJ6”中）和教练名称（在单元格“B7”到“B86”中）以便于交叉引用
I would like to add a 'mini' horizontal scroll bar (located in cell A4:B4) which would behaviour exactly like the main excel sheet scroll bar (i.e moving the sheet left or right). I have tried the ActiveX scroll bar and ‘Googled' solution for over 4hrs with no luck. Could an expert VBA programmer or Excel guru suggest a solution or spost some sample code? Cheers!!
我想添加一个“迷你”水平滚动条（位于单元格 A4:B4 中），它的行为与主 Excel 工作表滚动条完全相同（即向左或向右移动工作表）。我已经尝试了 4 小时以上的 ActiveX 滚动条和“Googled”解决方案，但没有成功。专家 VBA 程序员或 Excel 大师能否提出解决方案或发布一些示例代码？干杯！！
采纳答案by Tim Williams
For an ActiveX scrollbar:
对于 ActiveX 滚动条：
'in the sheet code module
Private Sub ScrollBar1_Change()
ActiveWindow.ScrollColumn = 4 + Me.ScrollBar1.Value
End Sub
Private Sub ScrollBar1_Scroll()
ActiveWindow.ScrollColumn = 4 + Me.ScrollBar1.Value
End Sub
Set the scrollbar min to 1 and the max to however many columns you have...
将滚动条的最小值设置为 1，将最大值设置为您拥有的列数...
EDIT: if you wanted the scrollbar to move along with the columns then you could do something like this in the Changeevent (but if you add it to the Scrollevent it's going to produce some odd results:
编辑：如果您希望滚动条与列一起移动，那么您可以在Change事件中执行类似的操作（但如果将其添加到Scroll事件中，则会产生一些奇怪的结果：
Private Sub ScrollBar1_Change()
Dim sc As Long
sc = 4 + Me.ScrollBar1.Value
ActiveWindow.ScrollColumn = sc
Me.ScrollBar1.Left = Me.Cells(1, sc).Left
End Sub
BUT - if you then manually move the sheet (via the arrow keys etc) then the scrollbar will move out of view, and there's no sheet_scroll event to catch to use to re-position the scrollbar.
但是 - 如果您随后手动移动工作表（通过箭头键等），则滚动条将移出视图，并且没有 sheet_scroll 事件可用于重新定位滚动条。
回答by himanshu
please put following code on sheet selection change
请将以下代码放在工作表选择更改上
Me.ScrollBar1.Value = ActiveWindow.ScrollColumn - 4
please adjust the columns which you want to scroll as fitt to active window then adjust the max scroll value for scrollbar1.
请调整您想要滚动到活动窗口的列，然后调整 scrollbar1 的最大滚动值。
