james123456
01-27-2012, 06:23 AM
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.
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.