通过旋转按钮启动另一个工作表 [英] Launch a different sheet by Spin button

问题描述

要使用旋转"按钮(ActiveX控件)显示上一页或下一页.单击该按钮时，事件已成功触发并激活了所需的工作表，但该工作表包含原始工作表中的某些元素(命令，图形等)，并将其显示为附加图片.

want to use a Spin button (ActiveX Control) to show a previous or next sheet. When the button is clicked the event is successfully triggered and the desired sheet is activated but it holds some elements (commands, graphs, etc.,) from the original sheet and shows these as an appending picture.

向下按钮事件的示例代码:

Sample code for Down button event :

Private Sub SpinButton_JumpToWeek_SpinDown()

Dim sh_num As String
Dim tmp_num As Integer

  Application.ScreenUpdating = False
  Application.EnableEvents = False

  SpinButton_JumpToWeek.Value = Range("B27").Value - 1
  tmp_num = SpinButton_JumpToWeek.Value

   ' Activate desired KTx sheet
  sh_num = "KT" & tmp_num

  Range("F27").Value = ""   'reset to blank

  Sheets(sh_num).Activate

  Application.ScreenUpdating = True
  Application.EnableEvents = True

End Sub

要覆盖此效果，我必须手动选择(激活)另一张纸，然后再次选择所需的纸.我还尝试通过宏使此变通方法自动化，但不幸的是，它不起作用.

To override this effect I have to manually select (activate) another sheet and then again select the desired sheet. I tried also to automatize this workaround with a macro, but unfortunately it does not work.

有趣的是，如果我在调试"模式下执行代码(使用断点和逐行步进)，则不会发生此问题.

It is interesting that this problem do not occur if I execute code in Debug mode (using breakpoint and the stepping line by line).

令人惊讶的是，如果我尝试通过将值(工作表名称索引)写入定义的单元格(即使用Worksheet_Change事件)来显示上一个/下一个工作表，则不会出现此类问题.正确显示所需的页面.查看照片.

Surprisingly, I do not have such problem if I try to show the previous/next sheet by writing a value (sheet name index) into a defined cell (i.e. using the Worksheet_Change event). The desired page is correctly shown. See photos.

此事件输入图像描述的示例代码:

Sample code for this evententer image description here:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sh_num As String

  Application.ScreenUpdating = False
  Application.EnableEvents = False

  If Range("F27").Value > 0 Then
 ' Activate desired KTx sheet
   sh_num = "KT" & Range("F27").Value
   Range("F27").Value = ""   'reset to blank

   Sheets(sh_num).Activate
  End If


  Application.ScreenUpdating = True
  Application.EnableEvents = True

End Sub

我需要使用旋转"按钮，因为它速度更快，并允许我跳过某些工作表(例如，如果这些工作表没有数据).在32位和64位Excel中都会出现此问题.

I need to use the Spin button because it is faster and allow me to skip some sheets (for example if these do not have data). The problem occurs in both 32 and 64-bit Excel.

有人知道为什么会出现此问题以及解决方法吗?我必须在Excel中更改某些设置或系统属性吗?任何帮助将不胜感激.

Do somebody an idea why this problem is occurring and a workaround? Do I have to change some settings or system properties in Excel? Any help would be highly appreciated.

@mehow由于大量的单词，我在此补充我的赞扬.

@mehow I append my commenst here due to large number of words.

1. 我按照您的建议尝试了一个带有插入的活动控件"Microsoft Office Spreadsheet"的UserForm示例.我发现这不是解决我的问题的好方法，因为当用户将值插入单元格时，此类工作表的响应相对较慢(即使在像我这样的快速PC上).此外，这会使我相当简单的* .xlsm工作簿变得复杂，该工作簿有50多个工作表(每周工作表1个，然后将这些工作表的内容链接到主工作表)，完全可以满足我的基本需求(当然是此旋转按钮的例外.)

我认为可能有必要操纵某种系统属性(例如Application.ScreenUpdating技巧)，但是我没有足够的VBA知识来找到它.

In my opinion there is probably necessary to manipulate some kind of system property (like for e.g. Application.ScreenUpdating trick), but I do not have enough VBA knowledge to find it.

1. 要清楚地解释我的问题，我需要共享我的示例工作簿，但是我不知道如何将其上载到该论坛.不幸的是，由于我的评分较低，我无法在该论坛上成功上传/显示图片，这也有很大帮助.

顺便说一句，我也看不到有关该论坛其他问题的图片..是否会由于网络浏览器中缺少插件而发生此问题?

BTW, I cannot see images on other questions of this forum, too. . Could this problem occur due to missing plugins in a web browser?

推荐答案

您可以使用一个简单的技巧...在"Application.screenupdating = true"之前，可以插入两行:

You can use a simple trick... before the "Application.screenupdating = true" you can insert the two lines:

ActiveWindow.SmallScroll Down:=-100
ActiveWindow.SmallScroll Up:= 100

这篇关于通过旋转按钮启动另一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！