+ Reply to Thread
Results 1 to 3 of 3

Check box and date picker code to add dynamically

  1. #1
    Registered User
    Join Date
    01-03-2020
    Location
    Staffordshire, England
    MS-Off Ver
    excel for office 365 mso (16.0.12624.20348) 32-bit
    Posts
    32

    Check box and date picker code to add dynamically

    Hi - I'm devising an order processing system, so far user selects data on Quote form, generates quote which also generates Order button. If customer confirms order user clicks on Order button which generates confirmation pdf and draft email, all good. As part of this process I'd like an OrderProcessing workbook to be updated with a specific set of checkboxes and date picker cells to enable progress tracking of each order. The attached spreadsheet contains a basic example of what's required. I've tried a few bits of vba code which I can get to create check boxes but I need full control over each one. If the user clicks on place order it should be possible to open the OrderProcessing sheet and add values to each cell per row for that order. Where the cell in the attached says Auto fill this will be taken from the Order sheet for Order Ref, Component type and Description. All other check box and date picker cells will be the same. What will happen continuously after that is the user will update each check box to signify progress of the order over time. If anyone can explain how to just create a bunch of dynamic check boxes and date pickers with variable names and captions I should be able to deal with the autofill values. Hope I'm making sense - please shout if not. Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    2,552

    Re: Check box and date picker code to add dynamically

    Terrible idea
    Post a cutdown workbook of how you import order data into processing form.
    There has to be a simpler way merely using VBA/UserForm and use your sheet just as a pseudo database.
    torachan.

  3. #3
    Registered User
    Join Date
    01-03-2020
    Location
    Staffordshire, England
    MS-Off Ver
    excel for office 365 mso (16.0.12624.20348) 32-bit
    Posts
    32

    Re: Check box and date picker code to add dynamically

    I've got this working as I want it to, the code below finds the first empty row and adds six check boxes from column D to I. To keep track of orders I want to link each cell. The code below works when I use .LinkedCell = "D" & rownum + i + 5 but I'd like to set it via cell number so I can set the link cell to the same row but in columns AD to AI (or similar) so I can report on progress. I've tried using Cells and Ranges but not working, please assist. Thanks
    #rowout = wks1.Range("A1", wks1.Range("A1").End(xlDown)).Rows.Count
    rownum = rowout + 1
    colnum = 4

    Set ShtRng = Range(Cells(rownum, colnum), Cells(rownum, colnum + 5))
    Set WrkSht = Application.ActiveSheet
    For Each Rng In ShtRng
    If i = 1 Then
    caption = "Cut/made"
    ElseIf i = 2 Then
    caption = "Tubes"
    ElseIf i = 3 Then
    caption = "Square"
    ElseIf i = 4 Then
    caption = "Feet,rods,other"
    ElseIf i = 5 Then
    caption = "Supports"
    ElseIf i = 6 Then
    caption = "Arms"
    End If

    With WrkSht.CheckBoxes.Add(Left:=Rng.Left, Top:=Rng.Top, Width:=Rng.Width, Height:=Rng.Height).Select
    With Selection
    .Characters.Text = Rng.Value
    .caption = ""
    .caption = caption
    .LinkedCell = "D" & rownum + i + 5 'this works
    '.LinkedCell = Cells(rownum + i + 10, 4) 'Range("$D$" & rownum + i + 5) 'Range(Cells(rownum, colnum + i + 10))
    i = i + 1
    End With
    End With
    Next
    wks1.Cells(rownum, 1).Value = "Auto fill"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Executing code after date is chaged with date picker?
    By RustyNail in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-20-2019, 12:23 PM
  2. Code to check if date being imported matches a previous date and time
    By bdouglas1011 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2018, 08:57 AM
  3. VBA Code to check Date
    By hkbhansali in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-09-2018, 03:59 AM
  4. Setting date picker date and having 2 columns to each date for excel calendar
    By bqheng in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-10-2017, 11:13 PM
  5. Replies: 8
    Last Post: 03-08-2017, 11:00 PM
  6. [SOLVED] code that will check the date from a table if its MM/YY is current and if not then date=YY
    By richzter in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-24-2014, 10:27 PM
  7. vetting of my code, and edit date picker on my form
    By omolemo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-26-2013, 10:02 AM

Tags for this Thread

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