+ Reply to Thread
Results 1 to 9 of 9

Userform - Select range and name the cells

  1. #1
    Forum Contributor
    Join Date
    03-24-2009
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    178

    Userform - Select range and name the cells

    Hi all,

    I hope someone could help me to get my userform to work please?

    I have created what I would like which is for the user to enter a name to call the 'daypart' definitions they want to use and then to be able to highlight the range they want it to refer to. The userform works but my problem lies with being able to write the code in the background linking the name they have entered to the range they have selected.

    If anybody is able to take a look at this and help me amend my code I would be most appreciative.

    Additionally I also need to sort the data by start time ascending which I am not too certain how to do and so if anyone could give me any advice on this too, it would be great.

    Many thanks for your help.

    Jessica

    PS Sorry, workbook is attached!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,606

    Re: Userform - Select range and name the cells

    I would do the first bit like this:
    Please Login or Register  to view this content.
    For the sorting, have you tried recording a macro while you do it manually?

  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Userform - Select range and name the cells

    I like xls- workbooks.



  4. #4
    Forum Contributor
    Join Date
    03-24-2009
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    178

    Re: Userform - Select range and name the cells

    Sorry snb, I keep forgetting how not everyone has excel 2007!

    Thanks StephenR for the code, it works and is fantastic.

    Re. the sorting, yes I have recorded a macro and tried to amend it but to be honest I got totally lost, I think it is working within the named range which is confusing me.

    There are two final steps I could really do with some advice with if anyone could help please?

    I need to sort the range selected, start time ascending.
    Then, if the first start time <> 00:00:00 Then add a row with the start time as 00:00:00 and the daypart definition the same as the last (bottom) daypart definition.

    If anyone could help me with these final steps, it would be fantastic please?

    Thank you!!

    (Attached updated, compatable workbook)
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    03-24-2009
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    178

    Re: Userform - Select range and name the cells

    Hi all,

    Just bumping this up in case anyone can help.

    Thanks

  6. #6
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Userform - Select range and name the cells

    I can't figure out what you want the code to do (what the result has to look like), nor why.

  7. #7
    Forum Contributor
    Join Date
    03-24-2009
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    178

    Re: Userform - Select range and name the cells

    Hi snb,

    Sorry if I haven't explained properly.

    We have to go through the list of times and say whether the time is in an 'afternoon', 'morning' segment etc. The definitions of afternoon etc vary and so the same time can have several definitions depending on which one we use.

    Currently what the office use is a vlookup formula to match the times to the tables I have put in the definitions sheet. I think there will be better ways but no one in the office has any knowledge of macros and are quite distrusting so until they are more confident that things are correct I decided to leave the methodology they want but to use the macro to automate the formatting etc and to enter the formulas. Currently they don't name any cells so I am putting that one out there!

    The named range will be used in my vlookup formulas and the heading of the column will be the same so that they should know which dayparts correspond with which table.

    I have got it all workings but at the moment they still have to do two initial steps to make it work - they have to sort the definitions table on start times ascending and if it doesn't start at midnight (00:00) they have to add a row to make it start from 0. Both of these steps are to make the v-lookup formulas work. I could easily make them do these steps and then run the macro but why do it if a macro can do it for you!

    I hope that makes sense, if not then let me know and thanks for your help.

    Attached is my spreadsheet including v-lookup etc.
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    03-24-2009
    Location
    London, UK
    MS-Off Ver
    Excel 2007
    Posts
    178

    Re: Userform - Select range and name the cells

    Sorry, I am just bumping this up in case anyone is able to help.

    Thanks

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Userform - Select range and name the cells

    See the attachment.

    NB. I replaced the original attachment in this post by a more sophisticated one.
    Attached Files Attached Files
    Last edited by snb; 09-16-2010 at 03:13 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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