+ Reply to Thread
Results 1 to 7 of 7

How to restrict VBA to Named Range?

  1. #1
    Registered User
    Join Date
    11-18-2020
    Location
    US
    MS-Off Ver
    10
    Posts
    3

    How to restrict VBA to Named Range?

    I have this code, and want to restrict it two 2 named ranges instead of the entire worksheet. What is best way to do this. Not very familiar with VBA.



    Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

    ActiveCell.Value = Now()

    Application.SendKeys "^{return}"


    Target = Format(Time, "h:mm:ss")

    Cancel = True


    End Sub

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: How to restrict VBA to Named Range?

    Something like:
    Please Login or Register  to view this content.
    Not sure what the Sendkeys command adds to this...?
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: How to restrict VBA to Named Range?

    Why are you using SendKeys?

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    11-18-2020
    Location
    US
    MS-Off Ver
    10
    Posts
    3

    Re: How to restrict VBA to Named Range?

    Thanks for your help, Jacc that worked.
    Not sure what the SendKeys does either.

    Honestly I copied this from somewhere else, and modified it to turn my worksheet into a data collection sheet that allows me to click a cell to add the time stamp and calculate the total duration of the row.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: How to restrict VBA to Named Range?

    From what I can see, I'm not entirely sure that SendKeys statement is valid. I assume it is meant to be, maybe, Crel-Enter.

    https://docs.microsoft.com/en-us/off...keys-statement

    Note that this
    Please Login or Register  to view this content.
    puts the Date AND Time in the Active Cell


    While this
    Please Login or Register  to view this content.
    puts the Time in the Target Cell


    The Active Cell and Target are the same cell, in this instance, so the second statement overwrites the first.

    So, all you really need is:

    Please Login or Register  to view this content.
    Or, my version:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-18-2020
    Location
    US
    MS-Off Ver
    10
    Posts
    3

    Re: How to restrict VBA to Named Range?

    Thank you for the help. I am familiar with SQL (self taught), but if I am decoding this properly it looks like I could put a second if statement before the end sub to perform a second function (ie enter y in active cell in a different named range when I double click the cell)

    BTW your option is much cleaner code. I really appreciate your feedback you provided.
    Last edited by heismanasu; 11-19-2020 at 02:19 AM.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: How to restrict VBA to Named Range?

    Yes, maybe this:

    Please Login or Register  to view this content.

+ 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. Listbox displaying named range B but Adding named range A to cell
    By ikkenieikke in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-05-2018, 02:27 PM
  2. [SOLVED] Compare cells to named range and create new sheets using related named range
    By drewship in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-10-2015, 03:35 PM
  3. [SOLVED] Assign named range to one cell depending on named range in another
    By x65140 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-22-2015, 11:04 AM
  4. Adding row to a named range-updating the named range address
    By kjsconv in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-12-2013, 11:22 PM
  5. Replies: 1
    Last Post: 02-27-2013, 12:00 AM
  6. [SOLVED] Determine what Named Range the Target Address is and return Named Range Name
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-18-2012, 10:49 PM
  7. Replies: 4
    Last Post: 02-12-2012, 11:41 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