drag VBA code ?

Hello,

I have this code for Checkbox on Userform :

Me.CheckBox1 = Application.WorksheetFunction.CountA(Sheet1.Range("C3,C4")) = 2

It tickes Checkbox If cell values are not blank. Code works great, but I need that code for several rows and columns, and different sheets (It's a monthly worksheet). Is there any way that I could drag code like formulas, or do I have to type this code for each Checkbox ?

Any suggestions ?

I'm not sure what this code means...

Let's say I have only column that need to be changed - I have cells ("C3,C4"), ("C8,C9") and ("C12,C13"). For each must be Checkbox ticked If Application.WorksheetFunction.CountA(Sheet1.Range()) = 2..

How can I create my code then ? There must be 3 Checkboxes, not only one !

I don't have only 3 ranges, I need that for 31 columns and 79 rows - which is exactly 2449 code entries with my existing code

Ok, how can I do that - "copy and paste then adjust the checkbox name and the range?" - Did you mean without VB coding at all ?

ranges are non-contigious as you can see !

Originally Posted by Andy Pope

Whilst you can not use VBA editor to do what you want you could use the worksheet to build your code syntax.

This example allows you to change control counter, column and sheet.

You then copy and paste result into your code.

Are you sure +2k checkboxes is the best approach to your problem though?

Ok, and how exactly did you manage this in your example, I don't see how you did It ?


I have attached my sample worksheet, maybe you will understand what I want to do and could give me some advice for different approach ?

Originally Posted by Andy Pope

It's simply formula using parameters to construct the text,

Consider a userform where you select a record by name, from a combo box. The you display information about each day for the user in a listbox.

Ok, how exactly did you mean that ? I don't want to display information about one user for whole month, I want to display information about all users each day.

I attached another sample worksheet and created Userform1, which is activated from rectangular shape with macro. Could you show me example of what you are thinking ?

Originally Posted by Norie

You could use a combobox to select the date and then have a listbox list the information for all users for that day.

Or you could put all the information in a listbox with a column for the date.

Ok, where I can do that, in Combobox property or with VBA ?

Originally Posted by Andy Pope

userform with combobox for date and listbox for information display.

Private Sub cboDate_Change()

    Dim lngRow As Long
    Dim lngIndex As Long
    Dim lngCol As Long
    
    lngCol = cboDate.ListIndex + 5
    lngRow = 3
    With Sheet1
    
        For lngIndex = 0 To lisUsers.ListCount - 1
            lisUsers.List(lngIndex, 1) = .Cells(lngRow, lngCol)
            lisUsers.List(lngIndex, 2) = .Cells(lngRow + 1, lngCol)
            lngRow = lngRow + 4
        Next
    End With
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()

    Dim lngDay As Long
    Dim lngRow As Long
    
    With Sheet1
        For lngDay = .Range("E2") To .Range("AI2")
            Me.cboDate.AddItem Format(lngDay, "d/ (ddd)")
        Next
        
        Me.lisUsers.ColumnCount = 3
        For lngRow = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row Step 4
            Me.lisUsers.AddItem .Cells(lngRow, 2)
        Next
    End With
    
    cboDate.ListIndex = 0
    
End Sub

Thanks, that is great, much better than my solution , I think that would take me a month to figure out