+ Reply to Thread
Results 1 to 4 of 4

Userform only works on sheet 6

  1. #1
    Registered User
    Join Date
    02-26-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    29

    Userform only works on sheet 6

    Hi all,

    I had a problem similar to this before and you guys solved it, checked my last post and something is different and I cant solve this!

    I have a userform located on Worksheet 3, to search a staff number on worksheet 6 ("Raw Data"). For some reason it works whilst on sheet 6 and also running through the VBA editor, but NOT on worksheet 3! I've tried defining the worksheets early on in the code, but nothing!

    Any ideas?


    Please Login or Register  to view this content.
    I also need to have worksheet 6 hidden so people cant see it... So if there are any tips on how to hide then unhide a worksheet so that this search code can work that would be swell too!

    Many thanks, one and all!

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Userform only works on sheet 6

    You used

    Please Login or Register  to view this content.
    So we would expect you to prefix each cell ref with "."

    So

    Please Login or Register  to view this content.

    Should be:

    Please Login or Register  to view this content.

    Include this Subroutine into your code.

    Use
    Please Login or Register  to view this content.
    To stop screen updates and to speed up your macros
    Use
    Please Login or Register  to view this content.
    To start screen updates Error Messages etc

    Please Login or Register  to view this content.
    Last edited by mehmetcik; 05-06-2020 at 12:34 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    02-26-2020
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    29

    Re: Userform only works on sheet 6

    Perhaps I should have said where and what error I'm getting...

    I get an error on this line of code:
    Please Login or Register  to view this content.
    The error is:
    Run-tim error '1004':
    Method 'Range' of Object'_'Worksheet' failed

    Also I neglected to say in my original message, I'm trying to search a table that is formed using a power query, I hoped this wouldn't impact the code, but maybe?

    Thank you for the optimisations however. I usually include them at the end once I'm done, but I should really learn to build them in at the start!

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Userform only works on sheet 6

    You need to prefix all Range/Cells calls with the relevant worksheet:

    Please Login or Register  to view this content.
    Rory

+ 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. [SOLVED] Userform only works on sheet 2
    By JobsSteve in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-17-2020, 11:31 AM
  2. [SOLVED] Userform VBA only works when used on particular pages?
    By matrixpom in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2020, 11:07 PM
  3. Replies: 1
    Last Post: 07-02-2018, 07:32 PM
  4. [SOLVED] Userform Initialize (sometimes works, sometimes don't!)
    By Rajeshkumar R in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-18-2017, 01:57 PM
  5. [SOLVED] Userform initialize (sometimes works, sometimes don't!)
    By pepito_child in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-04-2016, 02:10 AM
  6. VBA command works in Userform but not in Worksheet
    By neurosis737 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-01-2011, 07:39 AM
  7. userform only works on my PC
    By Canuckbulldog in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-22-2007, 08:40 AM

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