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]

Thanks Norie. Looking forward to it.

 

[James123456]

Hi Norie,

Any luck with this please?

Thanks.

 

[James123456]

Hi Norie,

I dont hink Listview will work as I am using Office 2010 64bit and Micrsoft has discontinued it I believe.

 

[Norie]

Well here's something for a listbox.

Not great, just an example really.

http://www.box.com/s/5yd45qlxfoinp4gz6ukv

PS Why are you using Office 64 bit?

 

[James123456]

Hi Norie,

That works great...very impressive. Thank you.

Using 64bit office as there are other applications dependant on 64bit office dlls.

Update doesnt work on the sheet, also will it be possible to have the boxes fill up at the bottom as soon as you click on a row on the listbox instead of clicking edit?

Thanks alot again.

 

[Norie]

I know nothing changes on the worksheet, I wasn't sure what was supposed to happen, especially since your title mentions an SQL insert.

I did originally have the texboxes fill on a click in the listbox.

I changed that, but I can't remember why exactly.

Should be easy to change back - the code to fill the texboxes is there.

Done it, and the file has been updated.

 

[James123456]

LOVELY.

Yes indeed it would be ideal to do a sql insert from what is changed on the form.

So if percentage is changed, then insert ID and percentage into a tmp table BUT if percentage is not changed then only insert the ID.

Also an indication of which rows has changed on the form would be brilliant in the form of colour change or a tick box next to them checked...anything visual.

Thank you Norie ever so much.

 

[Norie]

It's not possible to change the colour of individual items in a listbox.

To have checkboxes you would need to convert the lisbox to multiselect.

That would complicate adding/editing/deleting etc.

How about just having an extra column that indicates whether or not the percentage has been changed?

What exactly are you doing SQL-wise?

Are you inserting records or updating records?

 

[James123456]

Yeah having another column for indication would be perfect too.

I am truncating the tmp table first then inserting.

Thank you.

 

[Norie]

If I get a chance I might look at that later but you should give it a try yourself, all you really need to do is change the column count.

I asked about the SQL because I was wondering why you are only using the ID if the percentage hasn't changed.

I was also wondering why only 2 fields in the temp table when you seem to have more fields in the data.