Userform Dynamic TextBoxes/Labels/Checkboxes and SQL Insert

[James123456]

Hi all,

I have a spreadsheet and the purpose is to create a dynamic userform, show all values from Columns N (ID) and O (Name) as lebels and column P (Percentage) as editable textboxes from Sheet1 and create checkboxes at the end of each row on the userform.

The user then changes these percentages on the userform, as sson as the textbox is changed the checkbox become chekced. When clicking OK button if the percentage value has been changed then copy the new value to column Q on sheet1 and create a sql insert statement to insert the changed percentage with the ID into a table but if the textbox percentage has not been changed then only insert the ID.

So far I have manged to create the dynamic userform with all the labels, textboxes and checkboxes that displays the values from columsn N, O and P (With some online search and help). The code is as below:

Private Sub UserForm_Activate() 
    Dim lbl 
    Dim chkbox As MSForms.CheckBox 
    Dim txtbox 
    Dim Control As Control 
    Dim fields As Range 
    Dim field As Range 
    Const top1 = 32 
    Dim counter 
    counter = 1 
    Dim ws As Worksheet 
    Application.EnableEvents = False 
    Set ws = Worksheets("Sheet1") 
    Set fields = Worksheets("Sheet1").Range("N2", Worksheets("Sheet1").Range("N1048576").End(xlUp)).SpecialCells(xlCellTypeVisible) 
    For Each Control In Me.Controls 
        If Not Control.Top = 6 Then 
            On Error Resume Next 
            Me.Controls.Remove (Control.name) 
            On Error Goto 0 
        End If 
         
    Next Control 
    For Each field In fields 
        If Not field.Value = "ID" Then 
            Set lbl = Me.Controls.Add("Forms.Label.1", "N" & (counter + 1)) 
            With lbl 
                .Caption = field.Value 
                .Left = 6 
                .Top = top1 * counter 
                .Width = 94 
                .Height = 20 
                 '.Font.Bold = True
                .Font.Size = 12 
                .Font.name = "Times New Roman" 
                .TextAlign = fmTextAlignLeft 
                .BorderStyle = fmBorderStyleSingle 
            End With 
             
            Set lbl = Me.Controls.Add("Forms.Label.1", "O" & (counter + 1)) 
            With lbl 
                .Caption = field.Value 
                .Left = 96 
                .Top = top1 * counter 
                .Width = 115 
                .Height = 20 
                 '.Font.Bold = True
                .Font.Size = 12 
                .Font.name = "Times New Roman" 
                .TextAlign = fmTextAlignLeft 
                .BorderStyle = fmBorderStyleSingle 
            End With 
             
            Set txtbox = Me.Controls.Add("Forms.TextBox.1", "P" & (counter + 1)) 
            With txtbox 
                .Text = field.Offset(0, 2).Value 
                .Left = 210 
                .Top = top1 * counter 
                .Width = 78 
                .Height = 20 
                 '.Font.Bold = True
                .Font.Size = 12 
                .Font.name = "Times New Roman" 
                .TextAlign = fmTextAlignLeft 
            End With 
            Set chkbox = Me.Controls.Add("Forms.CheckBox.1", "Q" & (counter + 1)) 
            With chkbox 
                .Caption = field.Offset(0, 3).Value 
                .Left = 310 
                .Top = top1 * counter 
                .Width = 84 
                .Height = 24 
                .Tag = "CheckBox" 
                .Font.Bold = True 
                .Font.Size = 12 
                .Font.name = "Times New Roman" 
            End With 
             
            counter = counter + 1 
             
        End If 
    Next field 
     
    If counter > 10 Then 
        Me.Height = top1 * 10 + top1 
        Me.ScrollBars = fmScrollBarsVertical 
        Me.ScrollHeight = top1 * counter + top1 
    Else 
        Me.Height = top1 * counter + top1 
        Me.ScrollBars = fmScrollBarsNone 
    End If 
     
    Application.EnableEvents = True 
    making = False 
End Sub

I have also managed to get the changed Percentage and copy it on the spreadsheet when OK button is clicked with the code below:

Private Sub Button1_Click() 
    Dim chkbox As Control 
    Dim name 
    Dim counter 
    Dim sheet As Worksheet 
    For Each chkbox In Me.Controls 
        If chkbox.Tag = "CheckBox" Then 
            If chkbox.Value = True Then 
                name = chkbox.name 
                counter = counter + 1 
                 
                Set sheet = Worksheets("Sheet1") 
                sheet.Range("Q" & counter) = Me.Controls(Replace(name, "Q", "P")).Text 
                 
            End If 
        End If 
    Next chkbox 
    Application.CutCopyMode = False 
    Unload Me 
End Sub

However it doesnt do what I am trying to do and I really need your help with this please.

It copies the changed percentages onto Sheet1 Column Q, however when OK button is clicked first it copies the changed percentage into first cell of column Q (Q1), I want this to be copied on the second cell (Q2) as Q1 is the header.

Second problem is that when I change percentages, I would like the checkbox next to it to become checked automatically.

Third issue is that when I have changed the percentages and checked the checkbox manually, press OK then the changed percentages are copied to column Q but does not corespond to the correct row e.g change textbox 1, 2 and 4 on the userform, it should copy the changed percentages to cell Q2, Q3, Q5 but instead it copies it to Q1, Q2, and Q3.


Really really really appreciate all the help you can give me.

Thank you all in advance.

 

[James123456]

I am looking at it right now and willdefo give it a shot!

More data is there simply for users to know which ID belongs to which name.

There will be more calcualtion done if the percentage is changed and inserted with the ID on sql server but nothing if only ID is inserted into the tmp table.

Thank you.

 

[Norie]

I still don't get why you would need to include IDs in the table when you don't seem to be doing anything with them or the data they are associated with.

Probably missing something obvious though.

 

[James123456]

Hi Norie,

The values ID, Name and Percentage are retrieved from a sql table (Accounts) then displayed on the sheet/userform.

We change the percentages on the userform and insert the ID or ID and modified percentage into tmp table.

There are stored procedures on the sql server that looks at the tmp table and if there is ID and percentage present then it carries out calculations with the provided percentage for that particular ID but if only ID is present then it will exlude it from calculation all together.

Hope that is a bit clear.

Thanks.

 

[Norie]

Couldn't you not include the IDs which haven't had the percentage change and change the SP to just do the calculations on all the records in the temp table?

I suppose it wouldn't make much difference though, but it would kind of eliminate one, very small, step in the process.

Of course, I'm assuming you can change the SP.

 

[James123456]

Yes indeed Norie, I could change the SP but the ID without percentage is required as other processes are dependent on it.

This is just a portion of what I am trying to do.

Any chance of getting the finished veriosn Norie? When you get time obviously.

Thanks.