+ Reply to Thread
Results 1 to 4 of 4

When I edit a sheet using VBA, I can no longer tab through that sheet when I view it withi

  1. #1
    Registered User
    Join Date
    04-28-2024
    Location
    Langlois, Oregon
    MS-Off Ver
    2019
    Posts
    3

    When I edit a sheet using VBA, I can no longer tab through that sheet when I view it withi

    I have a userform in one workbook that opens a sheet in another workbook (name of other workbook is in public variable masterwb which is a full path name of type Variant)
    I can view and edit the workbook and all its sheets manually from within my application with

    Workbooks.Open (masterwb)

    At this point I can select cells, tab or arrow key through them and edit them as normal. Afterwards I close it with he following (MasterName is a string which is the workbook name extracted from the full pathname)

    Workbooks(MasterName).Close SaveChanges:=False
    ' False because I want the user to just view the sheet

    However, when I use another userform within my application to edit data on a sheet (wb is type Workbook, sfxList is type Range, lPosition is type Long)

    Set wb = Workbooks.Open(UserForm1.masterwb)
    Set sfxList = wb.Worksheets("Master").Range("B:B")
    sfxList.Cells(lPosition, 0).Value = TextBox1.Text
    Set sfxList = Nothing
    Workbooks(UserForm1.MasterName).Close SaveChanges:=True
    ' I also tried wb.Close SaveChanges:=True

    I can still view the workbook from the original userform with the same code

    Workbooks.Open (masterwb)

    Now the scrollbars work and I can select cells, but I can no longer edit it or tab through it. Can someone tell me what changed and how I can restore the ability to tab through this sheet normally?

  2. #2
    Registered User
    Join Date
    04-28-2024
    Location
    Langlois, Oregon
    MS-Off Ver
    2019
    Posts
    3

    Re: When I edit a sheet using VBA, I can no longer tab through that sheet when I view it w

    On further investigation, I don't need to edit the workbook to create this behavior. Just switching to another uerform and back causes the tab behavior to change, even though the target workbook is closed while changing userforms.
    Last edited by Chief Rick; 06-21-2024 at 02:23 PM.

  3. #3
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,425

    Re: When I edit a sheet using VBA, I can no longer tab through that sheet when I view it w

    The likely reason is that you open the first UserForm as a non-modal and the second as a modal.
    From the code level, you open the form as non-modal like this:
    Please Login or Register  to view this content.
    while opening as modal in this way (2 ways shown):
    Please Login or Register  to view this content.
    If in your code, when opening the form, there is no vbModeless argument, and yet the form is non-modal, it means that this was set manually when designing the form - the ShowModal property is set to False.

    Artik

  4. #4
    Registered User
    Join Date
    04-28-2024
    Location
    Langlois, Oregon
    MS-Off Ver
    2019
    Posts
    3

    Re: When I edit a sheet using VBA, I can no longer tab through that sheet when I view it w

    Bingo! You hit the nail on the head Artik! Thank you SO much. I originally openened userform1 as modeless but when I went back to it, I didn'tspecify so it was modal. Once I specified Modeless, everything worked as I wanted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Filtering pivot tables in co-authored workbook
    By Excel_apprentice in forum Excel General
    Replies: 0
    Last Post: 04-17-2024, 08:00 AM
  2. [SOLVED] In a Macro Sheets Want to View One Sheet for sometime more before Moving to Other Sheet
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 28
    Last Post: 09-07-2022, 07:17 AM
  3. Printing Hidden Sheets, changes sheet view to different sheet
    By gtnilc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-18-2021, 04:25 AM
  4. Replies: 1
    Last Post: 04-07-2020, 07:26 AM
  5. Replies: 2
    Last Post: 03-23-2009, 10:17 AM
  6. Replies: 1
    Last Post: 03-27-2006, 04:35 PM
  7. Password is required to view and edit every sheet
    By Jim333 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-24-2005, 05:00 AM

Tags for this Thread

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