Dynamic UserForms

[adambc]

There are a few posts that sort of relate to this, but I can’t find anything that addresses it directly?

I have a UserForm that has some static controls at the top and a “table”, with a header row using Labels and 5 columns (Date & Day using Lables, Enter1, Enter2 & Enter3 using TextBoxes) …

I’ve started to create Month specific UserForms, driven by another UserForm that opens when the Workbook opens which asks the User to select a Month from a ComboBox List …

But as well as having the correct number of “rows” (31, 30, or 28/29 for February) I need to match the Day to the Date (which will change year-on-year of course), add a BackColour to all the Controls that relate to Weekends and add some validation to the TextBoxes that relate to Weekends (Enter1 cannot have any value if it’s a Weekend) …

I can carry on doing that manually (I have created a 31 day version, which I’m then exporting/importing/editing to create the rest of the Months), but it is laborious and will need updating for each new year …

But I was thinking … could I “just” have some VBA code that creates the table (with all the formatting described above) that I could write once and know will survive the test of time?

Long shot I know, but the knowledge of the contributors to this forum never ceases to surprise me! …

Thanks …

 

[mikerickson]

You could use Frames.
Inside one frame, put all the textboxes and other controls for a single row. Get them the size and location you like.
Then make 30 copies of that Frame.

Then once you have your BaseDate you could write code like

For i = 1 To 31
    With Userform1.Controls("Frame" & i)
        If (Format(BaseDate + i - 1, "ddd") = "Sat") or (Format(BaseDate + i - 1, "Sun")) Then
            .Controls(1).BackColor = vbYellow
        Else
            .Controls(1).BackColor = vbWhite
        End If

        .Visible = (Month(BaseDate + i - 1) = Month(BaseDate))
    End With
Next i

Which sets the formatting of weekends different calculates where that row is needed (visible) for that BaseDate.

IF you are familiar with custom Classes, this would be a great use for custom objects.

 

[adambc]

You could use Frames.
Inside one frame, put all the textboxes and other controls for a single row. Get them the size and location you like.
Then make 30 copies of that Frame.

Then once you have your BaseDate you could write code like

For i = 1 To 31
    With Userform1.Controls("Frame" & i)
        If (Format(BaseDate + i - 1, "ddd") = "Sat") or (Format(BaseDate + i - 1, "Sun")) Then
            .Controls(1).BackColor = vbYellow
        Else
            .Controls(1).BackColor = vbWhite
        End If

        .Visible = (Month(BaseDate + i - 1) = Month(BaseDate))
    End With
Next i

Which sets the formatting of weekends different calculates where that row is needed (visible) for that BaseDate.

IF you are familiar with custom Classes, this would be a great use for custom objects.

Really like this but struggling setting BaseDate (through lack of knowledge!) - can you help please?

 

[adambc]

Really like this but struggling setting BaseDate (through lack of knowledge!) - can you help please?

IGNORE THIS PLEASE - me not thinking it through!

 

[adambc]

You could use Frames.
Inside one frame, put all the textboxes and other controls for a single row. Get them the size and location you like.
Then make 30 copies of that Frame.

Then once you have your BaseDate you could write code like

For i = 1 To 31
    With Userform1.Controls("Frame" & i)
        If (Format(BaseDate + i - 1, "ddd") = "Sat") or (Format(BaseDate + i - 1, "Sun")) Then
            .Controls(1).BackColor = vbYellow
        Else
            .Controls(1).BackColor = vbWhite
        End If

        .Visible = (Month(BaseDate + i - 1) = Month(BaseDate))
    End With
Next i

Which sets the formatting of weekends different calculates where that row is needed (visible) for that BaseDate.

IF you are familiar with custom Classes, this would be a great use for custom objects.

Have got this working and like it

You could use Frames.
Inside one frame, put all the textboxes and other controls for a single row. Get them the size and location you like.
Then make 30 copies of that Frame.

Then once you have your BaseDate you could write code like

For i = 1 To 31
    With Userform1.Controls("Frame" & i)
        If (Format(BaseDate + i - 1, "ddd") = "Sat") or (Format(BaseDate + i - 1, "Sun")) Then
            .Controls(1).BackColor = vbYellow
        Else
            .Controls(1).BackColor = vbWhite
        End If

        .Visible = (Month(BaseDate + i - 1) = Month(BaseDate))
    End With
Next i

Which sets the formatting of weekends different calculates where that row is needed (visible) for that BaseDate.

IF you are familiar with custom Classes, this would be a great use for custom objects.

Quick follow up question ...

Can I change the number of a Control in the Frame eg .Controls(1).BackColor = vbYellow is applied to the fifth Control which is a TextBox as opposed to a Label ...

I want the Controls to run 0, 1, 2, 3, 4, 5 as they appear in the frame ...

Or will I have to rebuild the Frame from scratch?

Thanks ...

 

[mikerickson]

You can change the 1 to the index of the control of interest, but AFAIK the order within the Controls collection can not be changed. after the control is made.

 

[adambc]

You can change the 1 to the index of the control of interest, but AFAIK the order within the Controls collection can not be changed. after the control is made.

That’s what I suspected, which causes a bit of fun copying/pasting Frames!

It doesn’t happen on every copy/paste, but on some the order of the Controls changes from 0, 1, 2, 3, 4 to 0, 1, 2, 4, 3 - so I’m having to test every copy/paste!!!

Apart from that, have now got everything working exactly as I wanted thanks to your original Reply (which I have marked as such) with a few tweaks …

THANK YOU …