5

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.

wrongly loaded sheet initial sheet with Spin buttons (right) 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.

enter image description here

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.

  1. 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?

mcerna2
  • 51
  • 4
  • have you considered using an UserForm and displaying parts of sheets on it? To get an idea see [this](http://stackoverflow.com/questions/17406319/display-a-part-of-an-excel-sheet-on-a-userform-using-vba/17407415#17407415) –  Aug 27 '13 at 13:34
  • What do you mean by `unfortunately it does not work` - what line does it fail on? – enderland Aug 27 '13 at 15:02
  • @mehow I wrote a VBA code which emulate my "manual" workaround but its response unfortunately was not effective – mcerna2 Aug 28 '13 at 06:12
  • @mehow I did not try with a UserForm yet, because I do not have a lot of experience (this is mine second excel-vba code project). However, I intend to learn it in a few weeks. – mcerna2 Aug 28 '13 at 06:20
  • @mcerna2 I do want to help you but I can't clearly see what your problem exactly is. Do you mind fixing your description? –  Aug 28 '13 at 07:05
  • @mehow I uploaded a Zip file contaning images and video of the problem 1 Mbyte to: http://filecloud.io/p3yjie52 – mcerna2 Aug 28 '13 at 14:44
  • @mcerna2 you cant upload a file directly to SO. you can however upload it on a free hosting provider and give us the link. It's impossible to reproduce the behavior for me. I need a sample file –  Sep 06 '13 at 10:38
  • @mehow I have upload a sample file to: http://filerio.in/4fbv721xmj06 ; The problematic Spin button is near a cell "Jump to week". Thanks a lot for help! – mcerna2 Sep 07 '13 at 21:37
  • I cant reproduce your problem. Everytime I jump to a sheet its all cleared with no leftovers from the PPM sheet. I am using Excel 2010 and win 7. I think this may be caused by Excel 2013 :/ –  Sep 09 '13 at 15:22
  • @mehow - I just retested it on an old Excel 2003 (WinXP ENG) and the problem is still present, but sometimes you need to scroll up/down or click somewhere into the KTx sheet to see that it is not properly loaded. Few months ago I installed Excel 2013 64-bit version first, but then I re-installed 32-bit version, because I read that with 64-bit there could be some problems with ActiveX controls... I also tried the file on some collegues PC with Excel 2010, its response is similar. Therefore, I think that problem is not related to Excel 2013. I can show a new video to prove it... – mcerna2 Sep 09 '13 at 15:54
  • @mcerna2 I dont have that problem on my machine so it's hard to judge why this happens.... –  Sep 09 '13 at 16:00

1 Answers1

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

Jair Batista
  • 88
  • 4