+ Reply to Thread
Results 1 to 17 of 17

UserForm to select Row and Column from 2 prompts

  1. #1
    Registered User
    Join Date
    06-08-2008
    Location
    UK, South East
    MS-Off Ver
    2003 + 2007
    Posts
    16

    UserForm to select Row and Column from 2 prompts

    Hi there all - anybody able to help on this one?!?

    I have an appoinment calendar that is one month per worksheet
    - Column headings 09:00 - 20:00
    - Row headings 'days and dates of the month' (split into two merged cells of two)
    ****************See attachment 'Calendar Format.jpg'****************

    I have then created a UserForm which has two dropdown combo boxes

    ****************See attachment 'UserForm.jpg'****************

    What I want to do is have a way of using the dropdown boxes to select a column and row reference to enter the other data into the four lines in the outlined box area (see Calendar Format.jpg)

    I want to be able to select a date - and to have only the dates from the sheet showing would be a bonus! - and have the script use that as its row reference, followed by selecting the time and having it add the column reference, and so selecting the correct cell to be able to then add all the other pieces of data.

    I would just set it up so that it all worked by selecting the first cell, then adding, but I'm worried about other users not doing that and so causing things to be misplaced or overwritten!!

    Any ideas?
    I'm more than willing to change most things, but I do need to have those four pieces of info, and I only need to have a simple calendar.

    If anything is not clear, please ask and I'll try to clarify further!

    Thanks in advance for any and all help

    Forgot to add - In the pics I'm using 2007, but I want to be able to run this on 2000/2003 as that's what's in the offices and it's going to be shared on the server.

    Bird
    Attached Images Attached Images
    Last edited by Bird_FAT; 06-08-2008 at 12:22 PM.

  2. #2
    Forum Contributor
    Join Date
    03-15-2005
    Location
    North Carolina
    MS-Off Ver
    2003 & 2007
    Posts
    180
    Hi

    This function will return the beginning (Range) of the Date/Time Section for you to populate your entries. This will not solve all your problems, but it can get you started.

    Please Login or Register  to view this content.
    As you can see this refers to Sheet1, set it to your needs Even better is to pass it into the function like tDate and tTime.

    The values that go into tDate is the value in the Date combobox on the userform and tTime is the Time combobox.
    jtp

  3. #3
    Registered User
    Join Date
    06-08-2008
    Location
    UK, South East
    MS-Off Ver
    2003 + 2007
    Posts
    16

    Looks good

    I'll give this a go a little later (tea time now )

    Thanks a lot jtp, I'll let you know how it eventually turns out!


    Bird

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Bird_FAT,

    Welcome to the forum. Please read the forum rules below and then add the link to the cross post on the other forum

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  5. #5
    Registered User
    Join Date
    06-08-2008
    Location
    UK, South East
    MS-Off Ver
    2003 + 2007
    Posts
    16

    Cross posted

    Quote Originally Posted by VBA Noob
    Bird_FAT,

    Welcome to the forum. Please read the forum rules below and then add the link to the cross post on the other forum

    VBA Noob
    Fair odds!

    Cross posted HERE

    And 'YES' I do agree with the motives and meanings of your 'cross-posting' post - though I may bandy around in a few other forums before I'm finished - It's not that I wouldn't have passed on info, I usually post any good replies and links to other forums, but just AFTER I start getting replies, and I ALWAYS post final solutions, if, as and when they occur. Having said all that, I DO think that you are right in saying that the link is best put as soon as, so here it is, sorry if I ****** anyone off!
    Also listed as cross-posted on other site

    Bird
    Last edited by Simon Lloyd; 06-08-2008 at 05:12 PM.

  6. #6
    Registered User
    Join Date
    06-08-2008
    Location
    UK, South East
    MS-Off Ver
    2003 + 2007
    Posts
    16

    Where now?...

    Quote Originally Posted by jtp
    The values that go into tDate is the value in the Date combobox on the userform and tTime is the Time combobox.
    Sounding like a real Noob here (and I am), BUT how would I alter your code to get the tDate and tTime from Cells B6 and B7 on sheet "."?


    So far I had refigured to do the following

    Command button to run series:

    Open UserForm1 = Time, Date, Next and Cancel
    - Time is a standard Combo Drop Box and puts the time in Sheets(".").Range("B7")
    - Date opens UserForm3 = Calendar that takes initial date of active sheet as its month showing
    - UserForm3 puts a date in Sheets(".").Range("B6")
    - Next opens Macro1 and UserForm2
    - Cancel closes UserForm1

    'Next' to open Macro1 (your code, jtp) then UserForm2 = Client Name, Tel#, Tutor, Reason, New, Close
    - Client Name = Text box that enters directly into first cell (offset (0,0))
    - Tel # = As above, but in cell (offset (1,0))
    - Tutor = Dropdown Combo in cell (offset (2,0))
    - Reason as above, but in cell (offset (3,0))
    - New = Open UserForm1
    - Close = Close

    It looks nice and I've got all the forms to run, the New and Close buttons working, the Calendar opening to the active sheet's month, etc; but I'm going to have to fiddle a bit with code to get it all to run smoothly.

    What do you all think? To fussy, simpler methods available? Hit me with your thoughts guys!!

    Bird

    (this will also be placed in the cross-post!)

  7. #7
    Forum Contributor
    Join Date
    03-15-2005
    Location
    North Carolina
    MS-Off Ver
    2003 & 2007
    Posts
    180
    Well I probably would not store the date and time on a worksheet but to keep you from making too many changes try this instead.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    06-08-2008
    Location
    UK, South East
    MS-Off Ver
    2003 + 2007
    Posts
    16

    Ideas

    jtp,

    So, how would you do it?

    Trust me, I'm still a definite Noob here, so I'm more than willing to take advice, in fact I'm positively looking for it!

    The more I can learn from guys like you, the more I want to learn, so let me know how you would do it, please!

    Bird

  9. #9
    Forum Contributor
    Join Date
    03-15-2005
    Location
    North Carolina
    MS-Off Ver
    2003 & 2007
    Posts
    180
    I would set it up where the userform (I believe UserForm1) would pass the date and time directly to the function I created.

    Add your code in the userform here and I will modify it as an example.

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    A refedit control allows the user to select a cell or range.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  11. #11
    Registered User
    Join Date
    06-08-2008
    Location
    UK, South East
    MS-Off Ver
    2003 + 2007
    Posts
    16

    Code so far

    Quote Originally Posted by jtp
    I would set it up where the userform (I believe UserForm1) would pass the date and time directly to the function I created.

    Add your code in the userform here and I will modify it as an example.
    Here Goes then:
    Currently looks like this:

    UserForm1 has the Date, Time, Next, Cancel buttons

    Next:
    Please Login or Register  to view this content.
    Cancel:
    Please Login or Register  to view this content.
    Date moves to sheet "." and then opens UserForm3:

    Please Login or Register  to view this content.

    UserForm3 is a basic Calendar:

    Please Login or Register  to view this content.
    *************************
    I've been trying to get this to open in the month of the worksheet by not having
    Sheets(".").Select
    Range("B6").Select
    before ShowIt

    and putting
    Me.Calendar1.Value = "B4"
    which is the first date on each sheet

    So being able to parse this command into the final choice would be awsome as it would then stay on that sheet and not move onto the other and then back.
    *************************

    Time is a Combo list (labled CList_Time) with vales from "."$D$1:$D$12 - named 'Times' as the RowSource - the choice is entered into ".!B7":

    Please Login or Register  to view this content.
    ***********

    This now leads me into three new questions -
    1 - How would I insert these two responses into your earlier code
    2 - Where would I add the array into the VB code of the combo box, as opposed to having it on sheet "."
    3 - how come after selecting a time in the combo list, the list then has a number like 0.75 showing, instead of 18:00? it puts the correct number into cell "B7"?

    ***********

    Sorry for the time it took me to reply - I only seem to learn by throwing myself in at the deep end, grabbing some code, then disecting it so that I can understand and move on from there; so, I've been trying to digest what I've found so far, from both here and while coding the other parts of this project.

    I am sincerely grateful for your help jtp, I've already learned loads.

    Bird
    Last edited by Bird_FAT; 06-10-2008 at 06:10 AM.

  12. #12
    Forum Contributor
    Join Date
    03-15-2005
    Location
    North Carolina
    MS-Off Ver
    2003 & 2007
    Posts
    180
    Hi Bird,

    Ok lets get started. If you dont have an Ok button for UserForm1, add it and the following code goes in it. This is where we want to call the function i created. Should look something similar to this.

    Please Login or Register  to view this content.
    You will need to use the first version of this function I sent you. Now you should be able to pass startRange to whatever function you have to fill the sheet.

    For this part

    *************************
    I've been trying to get this to open in the month of the worksheet by not having
    Sheets(".").Select
    Range("B6").Select
    before ShowIt

    and putting
    Me.Calendar1.Value = "B4"
    which is the first date on each sheet

    So being able to parse this command into the final choice would be awsome as it would then stay on that sheet and not move onto the other and then back.
    *************************
    Sounds like you want to be able to select the sheet correlated to today's date and grab the value in B4...Correct? If so, what is the format for the names of your sheets? January, February, etc?

    For the questions you have at the bottom. 1 should be answered above. 2. If you want to fill the time combobox with the times use something like this in the Initialize event of the userform.

    Please Login or Register  to view this content.
    3. If the values are showing up as .75 instead of 18:00 then check the format for the range you are referncing. Make sure the formats are set to a time format.

    Hope this helps some.

    jtp

  13. #13
    Registered User
    Join Date
    06-08-2008
    Location
    UK, South East
    MS-Off Ver
    2003 + 2007
    Posts
    16

    Answers and pics

    Hey jtp,

    To clarify the points that I haven't made clear

    For the date,
    I don't have a combo box - I want to be able to use this for future events and add an array into your original code to search all sheets for the specified date Array(sheets(all)) - So I'm calling up a basic Calendar, which is currently adding the date to cell ".!B6"

    OK button
    I have a button labled 'Next' that I use instead of OK, so no problems there

    Sheet naming format
    I have the sheets named as
    June'08
    July'08
    etc - 'full month name, followed by '08 for year'
    Is there a better way to name it for vb? i.e. MMMM/YYYY format?
    But then, if the range is being parsed to the Function, then I can use the original Me.Calendar1.Value = "B4" without a problem as it will grab the date from the sheet that is used to open the command (there is a command button on each sheet)

    Knowing that the old addage is true, here are the pictures (saves me a thousand words - and shows you what rubbish I've entered into my code ) Hopefully they are clear enough to read!!

    I'm getting closer to understanding all this, but obviously not quite getting it all ... yet! But, I'll persevere; and I hope you will too!

    Bird
    Last edited by Bird_FAT; 06-10-2008 at 08:57 AM.

  14. #14
    Forum Contributor
    Join Date
    03-15-2005
    Location
    North Carolina
    MS-Off Ver
    2003 & 2007
    Posts
    180
    Ok I can answer a couple of the questions and i will get back to the rest around lunch time.

    To start with the UserForm3 updating the cell on Sheet("."). I understand your question a little after seeing it. Replace the following

    Remove the Sheet(".") activation
    Please Login or Register  to view this content.
    This will update the cell without the need for activating the sheet
    Please Login or Register  to view this content.
    In an hour or so, I will send you the function that will select the sheet based on the date selected. Unless someone else beats me to it.

  15. #15
    Registered User
    Join Date
    06-08-2008
    Location
    UK, South East
    MS-Off Ver
    2003 + 2007
    Posts
    16

    Talking One more thing learnt!

    Quote Originally Posted by jtp
    This will update the cell without the need for activating the sheet
    Please Login or Register  to view this content.
    I get it - it tells the UserForm where to enter the data, but without having to change the active view - Now I understand the bit in the Time combobox

    Please Login or Register  to view this content.
    By not using *.Select, I'm only asking for the data to be placed in that cell, not to view or choose it!

    That's great - I can use that in a couple of my first projects that I have been meaning to update - thanks jtp!

    Bird

  16. #16
    Registered User
    Join Date
    06-08-2008
    Location
    UK, South East
    MS-Off Ver
    2003 + 2007
    Posts
    16

    And

    That also means that I can add
    Please Login or Register  to view this content.
    To the Calendar again!

  17. #17
    Registered User
    Join Date
    06-08-2008
    Location
    UK, South East
    MS-Off Ver
    2003 + 2007
    Posts
    16

    Exclamation UPDATE - 22 June 2008

    The project has changed drastically since it first started and jtp has been giving me a crash course in VBA at a level that is quite far from where I am now.

    Having said that, I have learned a hell of a lot in a small space of time, so I'm like a pig in sh!t at the moment - happy as happy can be!

    As and when it all gets finished I'll let you know how it turned out, and in what ways it changed! So, if this is a problem you want to see the solution too, then keep a bookmark here and I'll put the code up when it's finished!


    Bird

+ 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