Excel VBA滚动条可向左或向右移动工作表 [英] Excel VBA Scroll bar which shift sheet left or right

问题描述

请求：用于迷你滚动条的VBA代码，该代码使用VBA或ActiveX滚动条向左或向右移动工作表？

我创建了一个包含教练姓名的教练技能矩阵下方列出（在 B7至 B86单元格中），技能学科则在顶部列出（在E6至 AJ6单元格中）。在培训员姓名和技能相交的地方，我有一个下拉列表，指出 Y表示具有技能， N表示没有技能，而 n\a。如果该技能不适合该培训师。我已经冻结了标题行（在单元格E6到'AJ6'中）和培训师的名字（在单元格'B7'到'B86'中）以便于交叉引用

喜欢添加一个迷你水平滚动条（位于单元格A4：B4中），其行为与主Excel工作表滚动条完全相同（即，向左或向右移动工作表）。我已经尝试了ActiveX滚动条和 Googled解决方案超过4小时，但都没有碰到运气。 VBA专家或Excel专家可以提出解决方案或发布一些示例代码吗？干杯！

我的教练技能的屏幕截图矩阵

解决方案

对于ActiveX滚动条：

<$ p表单代码模块
中的$ p> 'Private Sub ScrollBar1_Change（）
ActiveWindow.ScrollColumn = 4 + Me.ScrollBar1.Value
End Sub

私人子ScrollBar1_Scroll（）
ActiveWindow.ScrollColumn = 4 + Me.ScrollBar1.Value
结束子


将滚动条的最小值设置为1，将最大值设置为有多少列...

编辑：如果您希望滚动条移动连同列一起，您可以在 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）。左
结束子
  

但是-如果您随后（通过箭头键等）手动移动工作表，则滚动条将移出视图，并且没有sheet_scroll事件可用于重新放置滚动条。

Request: VBA code for mini scroll bar which shift sheet left or right using VBA or ActiveX scroll bar?

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

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!!

Screenshot of my Trainer Skills Matrix

解决方案

For an ActiveX scrollbar:

'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...

EDIT: if you wanted the scrollbar to move along with the columns then you could do something like this in the Change event (but if you add it to the Scroll event it's going to produce some odd results:

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.

这篇关于Excel VBA滚动条可向左或向右移动工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！