1

I have multiple worksheets and wrote the function application.onkey in the sub workbook_activate() this function is supposed to open the UserForm on a button combination.

My problem is: If I press the button combination and the UserForm shows, it switches the sheet. No matter on which sheet I am it switches the sheet to always the same sheet. How can I prevent it from doing that?

I tried the following:

sub showingfrm()
dim tmpfrm as userform1
set tmpfrm = new userform1

    With tmpfrm
    .StartUpPosition = 0
    .Left = Application.Left + (0.5 * Application.Width) - (0.5 * .Width)
    .Top = Application.Top + (0.5 * Application.Height) - (0.5 * .Height)
    .Show
    End With

but this doesn't seem to work. (why I am using this code is because of multiple monitors)

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
abil vukiv
  • 11
  • 1
  • 1
    Please use the [edit] link below your question to add the code of the UserForm - most especially that which is executed when the form displays, such as the Initialize event. – Cindy Meister Aug 23 '18 at 08:07
  • 1
    maybe the problem lies in activating the sheet. Everytime I write something in VBA I try to avoid Sheet.activate or something like this as much as possible, another way might be to work with application.screenupdating = false/true – Mentos Aug 23 '18 at 08:07
  • 1
    The code you show doesn't switch a sheet. So you probably didn't show the relevant part of the code: Do you have code in the events `UserForm_Activate()` or `UserForm_Initialize()`? – Pᴇʜ Aug 23 '18 at 08:16
  • @Pᴇʜ I just looked but there is nothing like worksheet("whatever").activate in userform_activate() or Userform_initialize() – abil vukiv Aug 23 '18 at 08:21
  • @CindyMeister the code of the whole userform is more than 500 rows. but in initialize there is only the code which fills the combobox of the userform with every sheet of the workbook – abil vukiv Aug 23 '18 at 08:24
  • 1
    @abilvukiv OK, you must debug your code yourself if it is too long to show: Therefore please go through it step by step with F8 to figure out the line of code when the sheet switches. Then [edit] your question and add that relevant code part. – Pᴇʜ Aug 23 '18 at 08:26
  • 2
    @Pᴇʜ I have a suspicion. I wrote the function `private sub combobox3_change()` `worksheets(combobox3.value).select` `end sub` do you think that the problem lies there? – abil vukiv Aug 23 '18 at 08:29
  • 1
    Yeah you are right. A .select refocuses your worksheet to the selected item. Just tried it with some worksheets. Can you show us the code where you use .select or you know an alternative yourself? – Mentos Aug 23 '18 at 08:35
  • that exactly was the problem I commented it off and it works now without switching sheets. the problem is I want it to switch sheets when I am selecting a value on the box. – abil vukiv Aug 23 '18 at 08:44
  • 1
    @abilvukiv Please read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to understand how you can use worksheets (in the background) without selecting anything. – Pᴇʜ Aug 23 '18 at 08:45
  • thank you guys very much! @Pᴇʜ thanks for the link I'll read it. – abil vukiv Aug 23 '18 at 08:50

0 Answers0