Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    setting up input box or combobox to copy data based on selection

    I have a workbook where I manually enter data in a spreadsheet

    I would like to be able to select the following

    1) Employee name (from Drop down based on the names on the sheet C1, D1 etc)
    2) Date


    One the employee name and date has been selected, I need to select a number for e.g. 5

    One the above has been selected I need a tick copied in the first row containing the date selected and in the same column containing the name for eg Mark. This must be copied from the first date selected and copied down the number of times selected. Public holidays and Saturdays & Sunday to be excluded

    For eg if 02/01/2015 is selected and Mark has ben selected and the number 5 has been selected then cells C3, C6:C9 must contain ticks

    Your assistance in resolving this is most appreciated
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Howard,

    As I am sure you are aware, you will need to use data validation using a list to create your drop downs. As you have already done, the field area for the ticks has been changed to Webdings font. So what is left is that you need some code to place an "a" in the appropriate column based on name starting at the date you selected and copy down a number of times by some selected number excluding weekends and holidays. Is this correct?

    The holidays are on sheet 2 but from where will the code know what name, date, and number has been selected? This will need to be known so it can be incorporated into the code.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    Thanks for your reply

    I was thinking since receiving your reply, would be for the user to enter an "a" in the appropriate column in line with the first date and once entered a pop message to come up asking for a number for eg 10 and this to be copied by this number -9 (i.e the total including the manual tick to be 10.). The "a" (ticks) to exclude Saturdays, Sundays & public holidays

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Howard,

    Take a look at this spreadsheet. if the user places any character in a cell C2:J100, it will change to a tick and will count off 4 additional ticks below it in the same column excluding the weekends. Before I add in the popup and the holidays, would something like this work?

    In a worksheet module:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim row As Long, col As Long, count As Integer
        col = Target.Column
        row = Target.row
        count = 0
    If Not Intersect(Target, Range("C2:J100")) Is Nothing And _
        WorksheetFunction.Weekday(Cells(row, 1), 2) <= 5 Then
            Target = "a"
            Do While count < 4 And row < 100
                If WorksheetFunction.Weekday(Cells(row + 1, 1), 2) <= 5 Then
                    Cells(row + 1, col) = "a"
                    count = count + 1
                End If
                row = row + 1
            Loop
    End If
    Application.EnableEvents = True
    End Sub
    BTW, conditional ranges of =A2:J100 will suffice instead of breaking down into ranges
    Attached Files Attached Files
    Last edited by Maudibe; 2015-04-04 at 09:55.

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maudibe

    Thanks very much. Your code is what I am looking for. However, if the user has made a mistake, he/she must be able to remove the ticks

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Howard,

    I have encoded the procedure to remove the checks. If the user clicks on a check, they will be asked if they want it removed. I have add the holidays along with the weekends to be filtered out. Lastly, included the textbox to prompt for the number of checks.

    Let me know how this works out.

    Maud
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maud

    This works perfectly. Does this use quite a bit of resources? When I type in an "a" a second or third time , I need to close Excel and then re-open and it works

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Howard,

    I limited it only to the area C3 to J100. I believe it is the countif function that is doing numerous checks. Just wait until the code is finished but I will attempt to speed it up. You do not need to close it just wait.

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Just entering Application.ScreenUpdating = False on line 2 and Application.ScreenUpdating = True before End sub, sped it up greatly. See if that helps.

  10. #10
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    I think this is a bit simpler to just select the row and column desired>double click to enter count desired.
    Deleting can easily be done by selection or a simple macro to clear contents of the usedrange.offset(1,2)

    Option Explicit

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim col As Long
    Dim row As Long
    Dim count As Long
    Dim nr As Variant

    col = Target.Column
    row = Target.row
    count = 0
    nr = InputBox("Enter marks desired, ie: 5")
    If nr = "" Then Exit Sub

    Do Until count >= nr
    If Not Intersect(Target, ActiveSheet.UsedRange.Offset(1, 2)) Is Nothing And _
    Application.Weekday(Cells(row, 1), 2) <= 5 And _
    Application.CountIf(Worksheets("Sheet2").Range("$A $2:$A$22"), Cells(row, 1)) = 0 Then

    Cells(row, col) = "a"
    count = count + 1
    End If
    row = row + 1
    Loop
    Cells(row, col).Select
    End Sub
    Attached Files Attached Files
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  11. #11
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Maudibe


    Thanks for your input

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Don

    Thanks for the code. It works perfectly

Posting Permissions

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