Posts by RADCON

    I have been trying to make a label tell the user which range their selected cell happens to be in. The code to return a selected named range is correct, it just dosen't work for my circumstance ie. the technique I used to name the ranges. Can someone please help me modify the following:



    so that the following will return the name of the range containing the active cell



    NOTE: I have been calling this procedure from

    Code
    Sub Worksheet_SelectionChange(ByVal Target As Range) 'sheet 2007
    cellINBMs
    End Sub


    More info:
    1. If I used the preceding chunk of code on a new work book where I had named A1:B29 "first range" and use MsgBox str to display the named range when I click any cell in the range. it works!
    2. I named ranges in 4 sheets (2006, 2007, 2008, 2009) and as each range is named for a consecutive day, col A holds (7:00,7:15,(am)....till 11:00 (pm)). which is why each range is exactly 65 rows. Would nameing all ranges for all sheets in one big proceure make things work?
    3. What about the potential for 2 ranges named for the same date on different sheets? how can I modify my code to indicate the year in the range name also
    4. does my problem have to do something with dt=dateSerial...is there a format issue inside the range names? (I have almost ruled this out)
    5. Did the way I named my ranges use the first cell as a refrence cell or something, so that other cells are not in the range? I have been worried about this one the most, and have tried a million different things over the last week to figure it all out.


    Any new theories?...My application rules if I can get this to work!
    Thank You

    has anybody ever integrated access with a credit card reader. I have read a lot of books and cannot find anything useful on this topic. Any suggestions?

    Re: Communicate With Credit Card Reader


    I do not know, I simply assume that this is possible, Are there credit card readers that can communicate with excell? My credit card reader is about 8 years old, and is not hooked up to the computer. I guess my question is:
    Is it possible to find a card reader that will communicate with excel, if so who makes it? and Also, is there software that handles the transaction over my computers internet connection, or is it better to connect a stand alone unit and attempt a BeforeSwipe periferal event....I doubt that I am the first to have this problem but I do not know exactly which questions to ask.
    Thanks

    Does anybody have any suggestions on where to start with connecting my application to a credit card reader....I would eventually want to send the amount of sale to a credit card reader, then swipe the card, then have the amount of sale be appended to their current balance in the customers database (if the card is not declined, of course), and then the card reader logs on to visa and completes the transaction....maybe I can also have code that customizes the reciept before it is printed....I would love to avoid traditional POS systems and software.
    I have been to microsoft, and read a little about UPOS.NET, but there just isnot enough information
    Thanks for any help!

    Re: Remove Workbook Names


    Well this seems like a different approach, I have started trying to get your code to work.


    So naming ranges is not necessary? I want to be able to scroll down the sheet and select any day and have it tell me what day I selected in....I assume I can pass "Now" to your function and get it to scroll to that day....My floating user form has a label that always displays the current day. Buttons skip and scroll to the next day, next week or "today". I need to experiment with this for a bit.


    I still dont understand why a range A8:J74 can be refered to without a name


    Oh, and how do I use

    Code
    Debug.Print Selection.Address, nName.Name, nName.RefersTo


    I tried a few places, and in the immediate window with a ? infront but got an error


    Any further explination is greatly appreciated!
    Thank you

    I have sheet 2007 with 365 named ranges. the names are like "_20070225". I use an intersect procedure that converts the range name into "Sunday, February 25" and places it nicely in a label on a user form. To name the ranges on sheet 2007, my procedure used DateSerial (2007,1,1).
    I named another sheet 2008, changed the naming procedure to use DateSerial (2008,1,1), and while it works, the intersect procedure throws an error for either sheet.


    I assume it has something to do with hidden names, or the fact that all names for both sheets are in workbook.names.
    Here is the intersect procedure that I call from sub Worksheet_SelectionChange(ByVal Target As Range)



    Any ideas?
    Thanks:cool:

    Re: Name Of Range Containing Active Cell


    For anyone who cares, I finally figured it out..And I think I learned a thing or two....its been a long week.
    Thanks for all that helped me!


    : D

    Re: Name Of Range Containing Active Cell


    What is wrong with:


    Code
    MsgBox Format(DateSerial(Left(str, 2, 4), Mid(str, 5, 2), Right(str, 2)), "dddd, mmmm d")


    Where

    Code
    MsgBox str


    does return "_20070223"


    The ideal result would be "Friday, February 23"


    NOTE: The full block of code called Sub cellINBMs() can be found above and is called from Sub Worksheet_SelectionChange(ByVal Target As Range). FURTHER: the 365 ranges of 65 rows, a corresponding date for which each range was named, were named with a piece of code called Sub DefineNameandwatermark2007()


    Thank You:cool:

    Re: Name Of Range Containing Active Cell


    Ok, Here is where I am at.


    This works (finally)


    And the next part seems to work except for the last line


    Not quite sure how to modify that last line...any suggestions?
    Thank you.

    Re: Name Of Range Containing Active Cell


    So something like this


    But, With ActiveWorkbook.Names(X).RefersToRange gives me an error even when I set X = "_" & X the line before it

    Re: Name Of Range Containing Active Cell


    Now I got this to work



    Note: Using "yyyy" in "ddddmmmmd" instead of the "dddd" part throws an error


    Now I am getting an error in the following



    Should this second procedure work????
    Help, Please:cool:

    Re: Name Of Range Containing Active Cell


    ok, I think I see the problem...so I tried



    still get the error on last line of code. do I need to use "Alllication.WorksheetFunction.Text" in there too?
    Thanks

    Re: Name Of Range Containing Active Cell


    Thanks, Now gives me an error when it is to name the ranges


    Re: Name Of Range Containing Active Cell


    Thanks, I have tried a few things, My range naming procedure shows where I am hung up. How do I tell it to format DateSerial like"20070223"



    :cool:

    Re: Name Of Range Containing Active Cell


    Thanks shg, is there a way to convert "THU_FEB_22" ? I assume not, without a complicated string conversion procedure, would it have been possible to name my ranges with dates not converted to "THU_FEB_22", and then take out the _'s later? any suggestions to remedy the problem?
    THanks

    Re: Name Of Range Containing Active Cell


    I have been using



    and while there is no error, the last line of code returns THU_FEB_22 instead of "Thursday, February 22".
    How do I convert the string str to a date so that str can be formatted properly
    CDate str does not work
    Any suggestions?

    Re: Name Of Range Containing Active Cell


    Ok, I modified my range naming procedure so that any cell I select will return the name of the range. It was so obvious that I had been overlooking it for a long time. How can I get it to format the range name correctly.


    A previous post has my Sub cellINBMs() procedure and I use
    Format(str, "dddd, mmmm d") but it gives me "THU_FEB_22" instead of "Thrusday, February 22", which is what I want.....


    Why is it working but not formating?


    Thank you