+ Reply to Thread
Results 1 to 6 of 6

ThisWorkbook.ShowUserForm v. MyWrkBk!ShowUserForm

  1. #1
    Registered User
    Join Date
    04-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2000
    Posts
    38

    ThisWorkbook.ShowUserForm v. MyWrkBk!ShowUserForm

    I'm developing a workbook with two userforms. I have set them up with keyboard shortcuts using Tools/Macro/Macros

    Until recently it worked fine: I open the workbook, enable macros, and open the two forms with Ctrl+Q and Ctrl+L

    Recently, the shortcuts don't work reliably. Typically, on startup Ctrl+Q does nothing, but Ctrl+L works, but after opening the second form with Ctrl+L, the first one then opens fine with Ctrl+Q.

    Looking at Tools/Macro/Macros immediately after opening the workbook, the two entries have different forms: the one that works OK is shown as ThisWorkbook.ShowLabelsForm, and the other one is shown as BCS_MemData!ShowDataForm

    After using Ctrl+L to open LabelsForm, the entry for the Data form mysteriously changes to ThisWorkbook.ShowDataForm

    Here is the code in the Workbook:

    Please Login or Register  to view this content.
    What's going on and how can I ensure that both forms are available on startup? Any help will be much appreciated. I'm using Excel2000 on WinXP and on Vista.

    David

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Problem: ThisWorkbook.ShowUserForm v. MyWrkBk!ShowUserForm

    Hi,

    Try pointing the second macro shortcut to

    Please Login or Register  to view this content.
    otherwise it's a little difficult to comment without seeing the workbook in question.

    Rgds
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,183

    Re: Problem: ThisWorkbook.ShowUserForm v. MyWrkBk!ShowUserForm

    Move the code out of the Thisworkbook object and put in a standard code module.

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  4. #4
    Registered User
    Join Date
    04-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2000
    Posts
    38

    Re: Problem: ThisWorkbook.ShowUserForm v. MyWrkBk!ShowUserForm

    Thanks, Richard and Andy.

    Richard, I tried changing BCS_Memdata.xls! to ThisWorkbook. in the Tools/Macro/Macros dialog. But when I save and then close the workbook BCS_Memdata.xls, and then open it again, the macro still appears as BCS_Memdata.xls!ShowDataForm and doesn't work.

    Andy, I moved the code to a module, with the slightly modified code as you suggested. But now these subroutines don't appear in the Tools/Macro/Macros dialog, no matter whether I choose This Workbook or the file name BCS_MemData.xls in the listbox "Macros in:".

    So I'm stuck - unless there's a way of applying a shortcut to a VBA subroutine without using Tools/Macro/Macros.

    David

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,183

    Re: Problem: ThisWorkbook.ShowUserForm v. MyWrkBk!ShowUserForm

    I had no problem creating a workbook with the 2 routines and setting the shortcuts via the macro options dialog.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-28-2009
    Location
    UK
    MS-Off Ver
    Excel 2000
    Posts
    38

    Re: Problem: ThisWorkbook.ShowUserForm v. MyWrkBk!ShowUserForm

    Thanks, Andy,

    I now realise that the reason your method didn't work for me is that I had put "Option Private Module" at the head of my code module. I did this months ago at an early stage of this project. Now I removed it and all's well.

    Many thanks again,
    best wishes,
    David

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1