mardi 28 avril 2020

Excel Checkboxes and Arrays -VBA

Been skimming the sites that past week in search of answers for a VBA project that I am working. First time VBA programmer and a lot of the forums here have helped me get through it. I won't lie, my code doesn't look the best but so far the features i'm trying to add have been working but ultimately I've hit a road block with 2 things that maybe I'll be able to get some advice on.

I am creating a project tracker for the team I work with and what I want to do is copy all the project information for a single project in a single row. Then 2 columns in the row will be "Task Complete" and "Date Completed". Currently I'm using a Data Validation list to select "Complete" in the task complete cell. When "Complete" is listed, the Date Complete will auto populate with "Today's" date. I'm achieving this with just simple functions in excel.

I've then created VBA code so that a command button is clicked, it will highlight specific cells in the row, copy those cells, paste them in a empty worksheet, then clear the task and date complete cells. I've gotten most of these features to work but I've hit the roadblock in having excel copy the data in the blank work sheet in empty rows only (Row A1, A2, A3, etc). I am unsure how to have the code copy the data in empty rows. I know it can be achieved some how by using a variable (i) and loops.

My other question was, I originally wanted to use check boxes instead of a validated list but seem to have issues with formatting with the checkboxes. If my row size has to change to fit text, the checkboxes will crowd other cells. Is this just a flaw of forms/activeX in excel or am I missing part of the bigger picture.

As far as the code, I tried using an array to check the "Task Complete" column than individual If statements to add the date. Thanks in advanced for any help. Attached is a sample of my code:

Dim pjt As Worksheet
Dim datawks As Worksheet
Dim myBook As Workbook  'define worksheets and workboook
Set myBook = Excel.ActiveWorkbook
Set pjt = myBook.Sheets("Project Tracker")
Set datawks = myBook.Sheets("DATA")

Dim tskarray(16) As String
     tskarray(0) = Range("K4")
     tskarray(1) = Range("k5")
     tskarray(2) = Range("k6")
     tskarray(3) = Range("k7")
     tskarray(4) = Range("k8")
     tskarray(5) = Range("k9")
     tskarray(6) = Range("k10")
     tskarray(7) = Range("k11")
     tskarray(8) = Range("k12")
     tskarray(9) = Range("k13")
     tskarray(10) = Range("k14")
     tskarray(11) = Range("k15")
     tskarray(12) = Range("k16")
     tskarray(13) = Range("k17")
     tskarray(14) = Range("k18")
     tskarray(15) = Range("k19")
     tskarray(16) = Range("k20")


        If tskarray(0) = "Complete" Then
            Range("A4,B4,D4,F4,G4,J4,L4").Select
            Selection.Copy
            datawks.Select
            datawks.Cells(1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, skipblanks _
                :=False, Transpose:=False
            Sheets("Project Tracker").Select
            Application.CutCopyMode = False
            pjt.Range("J4,K4").Select
            Selection.ClearContents

        Else
        Debug.Print ("No Task to Complete")
        End If



Aucun commentaire:

Enregistrer un commentaire