Page 1 of 3 123 LastLast
Results 1 to 15 of 42
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Range, INDEX lookup (?); selecting dates, time/rooms for project meetings

    Good morning, loungers. I am not sure if this request for assistance is 'too big' for the lounge, but hopefully not. I am attaching a small zipped workbook. There are 3 sheets. In each Project worksheet (001 or 002), in column N, I need to be able to select a meeting date (MTG #1) and a time/room in column O (MTG #1 time/room). And do the same for columns P and Q. The worksheet 'MTG 1 and MTG 2' is a calendar showing each workday, with the time/room selections. If needed, people can call in and arrange meetings (date, time/room) for two different kinds of meetings (MTG 1 or MTG 2). The date and time/room have to be entered on the Project management sheet (Project 001, Project 002 etc--there will be 4 Project sheets).


    I have been working on this for quite a while, using different variations of drop-down menus etc where a time/room 'disappears' (through conditional formatting) but that is only a partial solution because I have to jump back and forth between the Project sheets and the 'MTG 1 and MTG 2' sheet to search for availability. I have concluded that it may require something like a huge INDEX formula (??) combined with some type of conditional formatting, so that if a time/room is selected on any given day, the worksheet 'MTG1 and MTG 2' no longer shows that time/room on that day as being available (ie. once it is booked, it comes off the sheet). And, there is already some 'worksheet change event' code in each Project worksheet if that prevents you from adding more code to each worksheet (if that's part of the solution).

    I have shown an example of how the dates and the time/room entries would look on Project 001 and on Project 002 (and have colored the selected dates & time/rooms on the 'MTG 1 and MTG2' sheet, for easy reference. Ideally, I would like to be able to click on a cell (say, Project 001 N25) and see the array of dates & time/room available and select a date and a time/room (say, Feb 8 for MTG #1 and 9:30 @ #821 for time/room)....and do it again for Project 001 P25 and select a date (eg. April 15) and a time/room (10:45 @ #1208)....and do the same type of thing for Project 002 (see dates and time/room selections on that worksheet) and as I make a time/room selection, that time/room 'disappears' so that it is not available to be selected again for that day. The extra tricky part is being able to do the same thing with Project 002 (see my samples of Feb 1, 10:45 @ #820 and March 10, 3:15 @ #1208).

    I want like to be able to post the 'MTG 1 and MTG2' sheet (or print it and email it) daily so that people can see (somewhat in real time) what times/rooms are available for meetings before the call asking to book a date & and time/room. Thank you for any help, or suggestions, as to how I can accomplish this....and if anyone has a better way of handling this, please say so.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Wouldn't using a different program be easier and more effective? I am not an expert on Outlook, but if you set up the meeting rooms as "people" and allowed others to view it and someone to edit it, could this be done in a relatively straightforward manner?

    Steve

    PS. I just looked in Outlook 2010 and it has a "Room Finder" feature with the calendar which sounds like what you want to do...
    Last edited by sdckapr; 2011-07-13 at 10:01.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve....hadn't thought of that b/c the group of people using this use Novel GroupWise, altho viewers could simply view, say, a .pdf version of the Calendar...and if they were to use OUTLOOK, it would be OUTLOOK 2003. I was trying to integrate this into a larger scheduling file and keep it all in Excel.....I was wondering if there were some VBA code that would work like this: say I have to enter a date for MTG #1 -- could I put my cursor on cell (eg. N25) and then go to the 'MTG 1 and MTG 2' worksheet, double click on the date of choice (say, Feb 8, 2011 from column A) and have it copy that date to cell N25...and to enter the time/room in O25, I then put my cursor on O25, switch to 'MTG 1 and MTG 2' sheet, double-click on the time/room of choice (picking 9:30 @ #821 from date row of Feb 8) ) and have it copy that data to O25 (and then use cond format to make '9:30 @ #821' on the 'MTG 1 and MTG 2' worksheet be white-on-white, so it doesn't show as available on that date...?......

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi again Steve....I am working on a suggestion that I saw elsewhere about data validation, but need to combine 'dates' and 'time/rooms' into a single cell...I am trying to use concatenate (a4,b4) or something like =a4&" -- "&b4 to join data from col A with data from col B....except that the date converts to the serial number (??) of the date (see attached sample)....is there a way to format the data in column C or D that will keep the date as a 'date' ?
    Attached Files Attached Files

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =TEXT(WORKDAY(A3,1),"mmm d")&" -- "&B4

    Adjust the formatting as desired.

    As to doing what you want, I imagine it would be feasible to use some dbl-click events, but I think way too cumbersome to program and the beyond the volunteer help here to do the coding and error handling. If you want to tackle it we could probably help with bits and pieces in your troubleshooting.

    I think the lookup could be done but you would need to store the date/time together in one column (and sorted) and use a 2nd column to have the scheduled meeting time (in excel time in units of days). You could then have the user give 2 pieces of information date/time[1] and meeting length (in days)[2], use match to lookup in the list of scheduled. You need to grab the Index row of the closest match < than the scheduled date/time. With that you can get the 3 more required pieces of info:1) the date/time of the closest meeting before your requested time[3], 2) the length of that closest meeting[4], 3) the next meeting after (indexrow +1)[5].

    With those 3 lookups and the date/time and length requested you can test if the requested meeting is possible. You must check 2 conditions and both must be true:
    The Before Date/time[3] + its length[4] must be <= requested date/time[1].
    The requested date/time[1] + its length[2] must be <= next meeting date/time[5]

    If it is more intuitive instead of start date/time and length, you could keep start date/time[3] and end date/time[6] and get a requested start date/time[1] and an end date/time[7]. then the 2 conditions are (since[3]+[4]=[6] and [1]+[2]= [7]:
    [6] <=[1]
    [7] <=[5]

    Hope that helps...
    Steve

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Here is some example code to do what you ask:
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Dim rTime As Range
      Dim rDate As Range
      Dim wMtgSheet As Worksheet
      Set wMtgSheet = Worksheets("MTG 1 and MTG 2")
      If Not Intersect(Union(Range("N13:N2872"), Range("P13:P2872")), _
        Target) Is Nothing Then
        wMtgSheet.Activate
        Set rTime = Application.InputBox( _
            Prompt:="Choose an Available Meeting Time/Room", _
            Title:="Choose time", _
            Type:=8)
        Set rDate = Cells(rTime.Row, 3)
        rTime.Interior.Color = vbMagenta
        rDate.Interior.Color = vbMagenta
        Me.Activate
        Target.Value = rDate.Value
        Target.Offset(0, 1).Value = rTime.Value
        Cancel = True
      End If
    End Sub
    Add it to the Project worksheet objects. When you dbl-click a cell in column N it will activate the Mtg1/2 sheet and prompt you to choose a time. Based on the time selected it will get the date from the row, color both the cells magenta then transfer the data to the N and O cells.

    You could have it strikethrough instead of coloring it, if desired...

    It should probably have some "validation" (make sure you are selecting in the right sheet, a valid column, one that is not magenta, etc, if you don't trust your workers to follow instruction [I leave that to you]).


    YOu can even make it "fancy" by hiding the Meeting 1 room times if col P is dbl-clicked and hide Mtg 2 times if COl N is chosen, etc [It will call your worksheet change events twice (when col n and o are changed) and if you do not want this, you should have the code disable and then reenable the events:
    To turn off:
    Application.enableevents = false
    to turn on:
    Application.enableevents = True

    Hope this helps,
    Steve

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve.,....my apologies for not getting back to you sooner, but some stuff came up and I had to set this aside for a bit....anyway, I wanted to tell you that the code you gave me looks great....I made some small changes to it (eg.I now have about 6 or 7 different columns that I have included so that I don't have to use drop-down menus anymore).....so far it seems to work great, altho every now and then, it develops a glitch......I think it could be caused my me double-clicking on a cell too quickly, so I have to play with it some more to do some testing....I have 1 question though--what does Type: 8 mean? I wondered if that was causing the glitch and changed it to 9, but it gave me an error message...so I put it back at 8, but I still wonder if that could be part of the problem? Any ideas? Thanks again...

  8. #8
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi again Steve.....I figured that I should post the code that I amended (see my previous post) as that might help explain what I am talking about -- what I did to simply things was to combine date + time + room (see my earlier question re concatenating) so that the user only makes one pick to choose meeting #1 date, time & room (col N).....and then meeting #2 date, time & room (Col O)...and then I added other ranges (eg. g, h, m, p, t, w, z, ac) that correspond to other data that also has to be entered.....maybe my 'description' about Steve's Super Selector etc etc is too long and that is causing the hiccup ??..or perhaps I am too quick to double-click ??...not sure, but the present code is:

    Private Sub Worksheet_BeforeDoubleClick(ByVal target As Range, Cancel As Boolean)
    Dim rTime As Range
    Dim rDate As Range
    Dim wMtgSheet As Worksheet
    Set wMtgSheet = Worksheets("Data Sheet")
    If Not Intersect(Union(Range("g13:g2872"), Range("h13:h2872"), Range("m13:m2872"), Range("n13:n2872"), Range("o132872"), Range("p132872"), Range("t13:t2872"), Range("w13:w2872"), Range("z13:z2872"), Range("ac13:ac2872")), _
    target) Is Nothing Then
    wMtgSheet.Activate
    Set rTime = Application.InputBox( _
    Prompt:="STEVE'S SUPER SELECTOR....Use this box to change the nature of the meeting, select a start time, a mediator, a rep, or, a JRD or other date", _
    Type:=8)
    Me.Activate
    target.Offset(0, 0).Value = rTime.Value
    Cancel = True
    End If
    End Sub

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What is the "glitch"?

    The 8 refers to the type of input (RANGE) that the inputbox accepts. (see the help on Application.inputbox, it is different than a normal inputbox).

    Steve

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you post an example file. This won't work with the previous ones since you have added a new sheet not in the original...

    Steve

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Hi Steve....sorry about the delay in getting back to you, but I was trying to work through this on my own...having some luck, but not all good....I will try to explain what I am doing. The original workbook that I was developing was becoming too large...it was up to almost 20MB and I am nervous that this is just an invitation to have the workbook 'go corrupt' on me.....so what I have done, and shown in the attached files, is this:

    (1) Workbook 1 (WB1) has Site 1000, Site 2000, Site 3000 and Site 4000 where job and work scheduling is done. There is VBA code on each worksheet that allows me to re-schedule a job by entering the 'new' date in col AG (this is code that I received from Don Wells in this forum a couple of years ago, and I have modified it to work here). There is also my modified version of the code you gave me in this thread (post #6) for setting meeting dates in col N and O......I really liked the way it worked, and even though I was using it for dates/times/rooms, I modified it to allow me to use it to also enter other data in col H, I, P, Q, U, X, AA and AD (eg, type of work, start time etc)rather than using drop-down menus, for entering type of work, start time, assigned foreman, customer name.....columns N and O involved selecting dates and times etc....your code mentions rTime as a range and has a value = rTime.value --- I have noticed the odd glitch when trying to enter something that is not a time and wondered if that was because I strayed from your original code? Or, could it be because some of the cells have data validation in them, as part of my original drop-down menu choices?

    (2) Originally, the dates, times and rooms for meetings were contained in WB1 (as part of the 'Data Sheet') but everything got way too big....so, I created a separate workbook containing the dates, times and rooms for meetings (called 'Meetings').....eventually, I will have WB1 and WB2, and I need to be able to have all 3 w/books open (WB1, WB2 and Meetings) and use your code in WB1 and WB2 to select type of work, start time, foreman, customer etc (and I think it should work pretty well as is.......I also want to be able to click col N or O (in any of the Site 1000, 2000, 3000, 4000 sheets in either WB1 or WB2) and 'jump' to the 'Meetings' w/book to select meeting dates, times and room from the 'Meetings' w/book and have it copy that cell's content into a cell in col N or O in WB1 or WB2 (like it did when everything was in 1 workbook).....and either color or strikethrough the cell of the date, time, room selected in the 'Meetings' w/book, so that that date/time/room can't be used again.

    (3) And, if possible, can you think of a way to take the 're-scheduling' code Don gave me and see if you can factor in the AM or the PM shown in col F of the Site worksheets..?.....you'll see that WB1 has a sheet called "Available worktime' that shows a summary of open time slots for each day at each Site......right now, if I re-sched something it looks for the first available open spot on the new date (whether that is AM or PM)....ideally, it would be nice to be able to re-sched from one Site to another Site, but that sounds pretty complicated and I would be quite happy simply to be able to re-sched within the same Site as I currently do, but also be able to specify not only a re-scheduled date, but also tell it to move the item to the first available open spot in either AM or PM (eg: from Jan 1/11 AM to Jan 7/11 AM, or Jan 7/11 PM etc etc)......if I can do this, then the 'Available worktime' worksheet showing all available time in one spot for all Sites will be much more meaningful (b/c right now, I have to flip through each Site w/sheet to see if the available time is in AM or PM).......any help you can manage is always greatly appreciated, and if you can think of a way to make things more elegant, please do so....thanks.
    Attached Files Attached Files

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I have been trying to understand your code and as a user when things happen, I am not sure what you expect them to do. I dbl click cells, the dbl-click code runs, but there seems to be instructions on what I should select or any validation. You have one code for multiple selections, but each seems to require a different routine...

    I will help as I can, but you must ask specific questions about problems with your code, explaining what you want it to do and where the code fails. If you need help with original code you need to be very specific about wha tthe code is meant to do...

    To answer some specific questions:

    ....your code mentions rTime as a range and has a value = rTime.value --- I have noticed the odd glitch when trying to enter something that is not a time and wondered if that was because I strayed from your original code? Or, could it be because some of the cells have data validation in them, as part of my original drop-down menu choices?
    What is the glitch? When does it occur and what happens. What do you expect to happen? The code I provided does nothing with the value other than place it into a cell. Whether the value is text, number, error or whatever is irrelevant to my code. If the cells you run the dbl-click event on have validation as well, you will have to look at that interaction. I suggest that if have code to enter a value, that the validation should be in the code. The data-validation in excel is to have validation without creating code, if you are going to use code, have the code do it all: you will have much more control over it.

    (2) Originally, the dates, times and rooms for meetings were contained in WB1 (as part of the 'Data Sheet') but everything got way too big....
    I don't see any questions in this part. Was that deliberate? You seem to providing us with insight of your future plans, but it does not seem you are trying to get any help with them. If you have specific questions you must ask them and provide as much detail as possible. You must realize we can create applications for you, only help with routines or particular parts of applications. You must create the applications which pull all the various routines together...

    (3) And, if possible, can you think of a way to take the 're-scheduling' code Don gave me and see if you can factor in the AM or the PM shown in col F of the Site worksheets..?
    I don't understand the question. What code? What does it do now, how do you want to incorporate col f?

    Some comments I will make is that your code all works on the "Active sheet" since you don't explicitly indicate when you do cells/range what sheet it is being done on. Since you seem to be working with multiple workbooks and worksheets, I would use variables to explicilty list the workbooks and worksheets being accessed.

    Instead of tables, some userforms would see to be better alternatives. I don't understand you logic of creating all possible dates/times/room combinations in a table. It would seem to make more sense to me to create a listing of what has been chosen (Room, start date/time and wither elapsed time or end date/time or assume all are fixed times and don't bother to store individually) and don't allow those to be picked. If the rooms and times fill up more than you have available, you can store the available rooms and times in a table. [Your tables don't seem to have the date/times stored in that fashion (but as text) making it difficult for lookups.]

    If you are going to create an application. map out what it will all do first. You seem to be creating and asking about "piece-meal" code and then you have changed the setup. Designing what you want and the logic should come first. Then create the tables (workbooks and worksheets) to best accomodate the logic you will be using. I don't understand the logic of your design. You must first design the application itself then code it to work as you want. You seem to have put the cart before the horse...


    It still seems to me that a commercial application (like Outlook) could do this out of the box. Most companies with Office have outlook available.



    Steve

  13. #13
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    Thank you, Steve, for you reply.I apologize if my previous post wasn't very clear -- reading your post, I understand your frustration (?) with what I previously posted. I am going to start at the beginning and try to lay out what I am doing and attach a couple of sample workbooks. As far as OUTLOOK goes, I would like to be able to use it, but the others involved in this little project with me don't have OUTLOOK and so that's why I am trying this route. Again, my apologies for your frustration and I will return and post in a more coherent manner, with some explicit questions etc.

  14. #14
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post
    HI Steve....I am back at this again, trying to explain what is happening and what I mean by a glitch. I am using the code you gave me in post #6 (above), although I deleted lines 2 lines (rTime.interior.color=vbmagenta and rdate.interior.color=vbmagenta) because I didn't want that to occur, and, I changed the TargetOffset (in the 4th last line) from 0,1 to 0,0.

    Your code named 2 ranges; I adapted that to name 10 ranges...otherwise, the code is as you gave it to me. I took a screen shot of the error message and a screen shot of the code that gets highlighted in yellow when I click the Debug button....I am attaching these, hoping that they will tell you what is going on when I encounter an error. Are you able to figure out what is happening and why? Thank you for your patience.
    Attached Files Attached Files

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Are your selecting a range/cell or typing something into the input box when this occurs. The input box is expecting you to select a range/cell.

    [If that is not the issue, attach a file that displays the problem and give me a detailed set of steps that recreates the error message...]

    BTW you don't need the offset:
    target.Offset(0, 0).Value = rTime.Value
    is the same as:
    target.Value = rTime.Value

    Steve

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •