Launch a different sheet by Spin button

Question

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

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

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.

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 I append my commenst here due to large number of words.

1. I followed your suggestion and tried the example of a UserForm with inserted active control “Microsoft Office Spreadsheet”. I found out that this would not be a good solution for my case, because the response of such sheet is relatively slow (even on a fast PC like mine) when user inserts values into cells. Furthermore, this would greatly complicate my fairly simple *.xlsm workbook, which has more than 50 sheets (1 sheet for each week, the contents of these sheets are then linked to a main sheet), and completely meets my basic necessities (with the exception of this spin button of course).

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.

2. To clearly explain my question I would need to share my sample workbook, but I do not know how to upload it to this forum. Unfortunately, I am not able upload/show images successfully on this forum (due to my low rating), which would also help a lot.

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

Answer 1

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