+ Reply to Thread
Results 1 to 4 of 4

How to call macros with a digit for certain sheets but use numbers as usually for others

  1. #1
    Registered User
    Join Date
    03-22-2024
    Location
    Cologne
    MS-Off Ver
    2019
    Posts
    2

    Question How to call macros with a digit for certain sheets but use numbers as usually for others

    Hello!
    Can you help me, please?
    1) I have several sheets where users have to insert only one digit into several cells.
    2) However there are other sheets where several digits have to be inserted into several cells by users.

    I would like to give the possibility to insert one digit and select the following cell automatically without pressing "Enter".

    I use both the numberpad and the numbers from the keyboard.

    My code in workbook_open:
    Application.OnKey "1", "insert_1"
    Application.OnKey "{97}", "insert_1"
    Application.OnKey "2", "insert_2"
    Application.OnKey "{98}", "insert_2"

    and so on.

    My code in a module:
    Sub insert_1()
    If ActiveSheet.Name like "abc*" then
    Activecell.Value = 1
    Else
    ...
    End If
    End Sub

    Sub insert_2()
    If ActiveSheet.Name like "abc*" then
    Activecell.Value = 2
    else
    ...
    End If
    End Sub

    and so on.

    That works fine.

    But If I want to insert digits into the other sheets nothing happens, because the condition doesn't match (other Sheet.Names).
    How can I continue my If part with else? Users should be able to use the numbers as usually. Is that possible?

    Thank you very much!

    Wolli

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: How to call macros with a digit for certain sheets but use numbers as usually for othe

    Instead of using Open event in thisworkbook module use SheetActivate
    Please Login or Register  to view this content.
    Then your code in standard module could be just:
    Please Login or Register  to view this content.
    and so on.

    PS: please see how CODE tags improve readability of the code
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    03-22-2024
    Location
    Cologne
    MS-Off Ver
    2019
    Posts
    2

    Smile Re: How to call macros with a digit for certain sheets but use numbers as usually for othe

    Hello Kaper!

    Thank you very much for the fast and perfect help!
    Problem is solved!

    Best regards,

    Wolli

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,623

    Re: How to call macros with a digit for certain sheets but use numbers as usually for othe

    Glad it worked for you. And thanks for a reputation point.

    As the problem is solved, it would be a good idea to mark also thread as Solved. Above your first post you will find Thread Tools to do it.

+ 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] Extract 1, 2 or 3-digit Numbers from Text String but Ignore 4-Digit Dates
    By S_Rycroft in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-09-2020, 12:03 PM
  2. Replies: 1
    Last Post: 04-18-2019, 01:35 AM
  3. [SOLVED] Macro to convert WBS single digit numbers to double digit numbers
    By LeanAccountant in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 06-09-2018, 05:25 AM
  4. [SOLVED] Selecting or Activating sheets which are named with 4 digit numbers
    By Rajkumar_h in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-29-2017, 11:54 AM
  5. Bank Recon - How to convert 8 digit formatted check numbers to 6 digit
    By Quisp in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-21-2017, 09:55 PM
  6. [SOLVED] macro to extract 9 digit numbers as well as alpha numberic 9 digit numbers from txt file
    By Raju Radhakrishnan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2012, 10:15 AM
  7. Cross check 7 to 8 digit numbers between two different sheets
    By aksptl in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-23-2009, 03:16 PM

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