ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Updating a userform (https://www.excelbanter.com/excel-programming/430611-updating-userform.html)

stevec
Updating a userform
 

I have currently a userform which I use to insert information. This userform
combines free text and and drop down menus. Once the userform is completed, I
use an insert button to show the information in a workbook.

I would like to be able to recall the userform, update the information for
the person I have selected, then update this information in the relevant row
in which this person appears.

Can anyone assist with a code to do this. I am new to coding, so anything
would be appreciated.

This is the code at the moment:




Option Explicit


Private Sub TextBox43_Change()

End Sub

Sub UserForm_Activate()
Application.ScreenUpdating = False

Dim Counter As Integer
Dim RowNum As Integer
Dim NoOfRows As Integer
Dim CounterA As Integer
Dim RowNumA As Integer
Dim NoOfRowsA As Integer
Dim CounterB As Integer
Dim RowNumB As Integer
Dim NoOfRowsB As Integer
Dim CounterC As Integer
Dim RowNumC As Integer
Dim NoOfRowsC As Integer
Dim CounterD As Integer
Dim RowNumD As Integer
Dim NoOfRowsD As Integer

RowNum = 3
NoOfRows = Application.CountA(Sheets("Personlist").Range("A:A "))
With Me.ComboBox1
For Counter = 1 To NoOfRows + 1
.AddItem Sheets("company").Cells(RowNum, 1)
RowNum = RowNum + 1
Next
End With

RowNumA = 3
NoOfRowsA = Application.CountA(Sheets("tables").Range("c3:c20" ))
With Me.ComboBox2
For CounterA = 1 To NoOfRowsA + 1
.AddItem Sheets("tables").Cells(RowNumA, 3)
RowNumA = RowNumA + 1
Next
End With


RowNumB = 3
NoOfRowsB = Application.CountA(Sheets("tables").Range("D3:D20" ))
With Me.ComboBox3
For CounterB = 1 To NoOfRowsB + 1
.AddItem Sheets("tables").Cells(RowNumB, 4)
RowNumB = RowNumB + 1
Next
End With

RowNumB = 3
NoOfRowsB = Application.CountA(Sheets("tables").Range("D3:D20" ))
With Me.ComboBox4
For CounterB = 1 To NoOfRowsB + 1
.AddItem Sheets("tables").Cells(RowNumB, 4)
RowNumB = RowNumB + 1
Next
End With

RowNumB = 3
NoOfRowsB = Application.CountA(Sheets("tables").Range("D3:D20" ))
With Me.ComboBox5
For CounterB = 1 To NoOfRowsB + 1
.AddItem Sheets("tables").Cells(RowNumB, 4)
RowNumB = RowNumB + 1
Next
End With

RowNumC = 3
NoOfRowsC = Application.CountA(Sheets("tables").Range("A1:A7") )
With Me.ComboBox8
For CounterC = 1 To NoOfRowsC + 1
.AddItem Sheets("tables").Cells(RowNumC, 1)
RowNumC = RowNumC + 1
Next
End With

RowNumD = 3
NoOfRowsD = Application.CountA(Sheets("tables").Range("N3:N20" ))
With Me.ComboBox9
For CounterD = 1 To NoOfRowsD + 1
.AddItem Sheets("tables").Cells(RowNumD, 14)
RowNumD = RowNumD + 1
Next
End With

End Sub
Private Sub ComboBox1_Change()
Dim r As Long
r = Me.ComboBox1.ListIndex + 3
Me.TextBox1.Value = Worksheets("Person").Cells(r, 2).Value
End Sub

Private Sub combobox3_Change()
Dim r As Long
r = Me.ComboBox3.ListIndex + 3
Me.TextBox26.Value = Worksheets("tables").Cells(r, 5).Value
End Sub

Private Sub combobox4_Change()
Dim r As Long
r = Me.ComboBox4.ListIndex + 3
Me.TextBox27.Value = Worksheets("tables").Cells(r, 5).Value
End Sub

Private Sub ComboBox5_Change()
Dim r As Long
r = Me.ComboBox5.ListIndex + 3
Me.TextBox28.Value = Worksheets("tables").Cells(r, 5).Value
End Sub

Private Sub ComboBox8_Change()
Dim r As Long
r = Me.ComboBox8.ListIndex + 3
Me.TextBox29.Value = Worksheets("tables").Cells(r, 2).Value
End Sub
Private Sub ComboBox6_Change()
Dim r As Long
r = Me.ComboBox6.ListIndex + 3
Me.TextBox29.Value = Worksheets("tables").Cells(r, 6).Value
End Sub
Private Sub ComboBox7_Change()
Dim r As Long
r = Me.ComboBox7.ListIndex + 3
Me.TextBox29.Value = Worksheets("tables").Cells(r, 6).Value
End Sub
Private Sub ComboBox9_Change()
Dim r As Long
r = Me.ComboBox9.ListIndex + 3
Me.TextBox42.Value = Worksheets("tables").Cells(r, 15).Value
End Sub


Private Sub CommandButton3_Click()
ActiveWorkbook.FollowHyperlink Address:="47"
End Sub

Private Sub CommandButton2_Click()
On Error Resume Next
Worksheets("tblPersonTable").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = TextBox1
ActiveCell.Offset(0, 4).Value = TextBox29 * 1
ActiveCell.Offset(0, 5).Value = Format(TextBox2, "mm/dd/yyyy")
ActiveCell.Offset(0, 6).Value = Format(TextBox3, "mm/dd/yyyy")
ActiveCell.Offset(0, 7).Value = TextBox42 * 1
ActiveCell.Offset(0, 8).Value = TextBox43 * 1
ActiveCell.Offset(0, 16).Value = TextBox4 * 1
ActiveCell.Offset(0, 17).Value = TextBox5
ActiveCell.Offset(0, 18).Value = ComboBox2
ActiveCell.Offset(0, 19).Value = TextBox26 * 1
ActiveCell.Offset(0, 21).Value = TextBox28 * 1
ActiveCell.Offset(0, 22).Value = TextBox6 / 100 * 1
ActiveCell.Offset(0, 23).Value = TextBox7 / 100 * 1
ActiveCell.Offset(0, 24).Value = TextBox8 / 100 * 1
ActiveCell.Offset(0, 25).Value = TextBox30
ActiveCell.Offset(0, 26).Value = TextBox9 / 100 * 1
ActiveCell.Offset(0, 27).Value = TextBox10 / 100 * 1
ActiveCell.Offset(0, 28).Value = TextBox11 / 100 * 1
ActiveCell.Offset(0, 29).Value = TextBox31
ActiveCell.Offset(0, 30).Value = TextBox46 / 100 * 1
ActiveCell.Offset(0, 31).Value = TextBox47 / 100 * 1
ActiveCell.Offset(0, 32).Value = TextBox44 / 100 * 1
ActiveCell.Offset(0, 33).Value = TextBox45
ActiveCell.Offset(0, 34).Value = TextBox35 / 100 * 1
ActiveCell.Offset(0, 35).Value = TextBox36 / 100 * 1
ActiveCell.Offset(0, 36).Value = TextBox37 / 100 * 1
ActiveCell.Offset(0, 37).Value = TextBox34
ActiveCell.Offset(0, 38).Value = TextBox41 / 100 * 1
ActiveCell.Offset(0, 39).Value = TextBox40 / 100 * 1
ActiveCell.Offset(0, 40).Value = TextBox39 / 100 * 1
ActiveCell.Offset(0, 41).Value = TextBox38
ActiveCell.Offset(0, 42).Value = TextBox15 * 1
ActiveCell.Offset(0, 43).Value = TextBox16 * 1
ActiveCell.Offset(0, 44).Value = TextBox17 * 1
ActiveCell.Offset(0, 45).Value = TextBox18 * 1
ActiveCell.Offset(0, 52).Value = CheckBox3
ActiveCell.Offset(0, 53).Value = CheckBox4
ActiveCell.Offset(0, 54).Value = TextBox24 * 1
End Sub

Private Sub label70_Click()
Worksheets("tblpersontable").Select

End Sub

Jacob Skaria
Updating a userform
 

Steve

Suppose you have the name in Column A; use the below code to get the row
number in which the person appears..Adjust the code;mention the search text
string or control name, mention sheetname etc; to suit...Once you get the row
update the other details..

Dim varFound As Variant, strSearch As String, lngRow As Long
strSearch = ComboBox1.Text
Set varFound = Worksheets("Sheetname").Range("A:A").Find(strSearc h)
If Not varFound Is Nothing Then lngRow = varFound.Row


For any more help refer http://www.contextures.com/xlUserForm01.html

If this post helps click Yes
---------------
Jacob Skaria


"SteveC" wrote:

I have currently a userform which I use to insert information. This userform
combines free text and and drop down menus. Once the userform is completed, I
use an insert button to show the information in a workbook.

I would like to be able to recall the userform, update the information for
the person I have selected, then update this information in the relevant row
in which this person appears.

Can anyone assist with a code to do this. I am new to coding, so anything
would be appreciated.

This is the code at the moment:




Option Explicit


Private Sub TextBox43_Change()

End Sub

Sub UserForm_Activate()
Application.ScreenUpdating = False

Dim Counter As Integer
Dim RowNum As Integer
Dim NoOfRows As Integer
Dim CounterA As Integer
Dim RowNumA As Integer
Dim NoOfRowsA As Integer
Dim CounterB As Integer
Dim RowNumB As Integer
Dim NoOfRowsB As Integer
Dim CounterC As Integer
Dim RowNumC As Integer
Dim NoOfRowsC As Integer
Dim CounterD As Integer
Dim RowNumD As Integer
Dim NoOfRowsD As Integer

RowNum = 3
NoOfRows = Application.CountA(Sheets("Personlist").Range("A:A "))
With Me.ComboBox1
For Counter = 1 To NoOfRows + 1
.AddItem Sheets("company").Cells(RowNum, 1)
RowNum = RowNum + 1
Next
End With

RowNumA = 3
NoOfRowsA = Application.CountA(Sheets("tables").Range("c3:c20" ))
With Me.ComboBox2
For CounterA = 1 To NoOfRowsA + 1
.AddItem Sheets("tables").Cells(RowNumA, 3)
RowNumA = RowNumA + 1
Next
End With


RowNumB = 3
NoOfRowsB = Application.CountA(Sheets("tables").Range("D3:D20" ))
With Me.ComboBox3
For CounterB = 1 To NoOfRowsB + 1
.AddItem Sheets("tables").Cells(RowNumB, 4)
RowNumB = RowNumB + 1
Next
End With

RowNumB = 3
NoOfRowsB = Application.CountA(Sheets("tables").Range("D3:D20" ))
With Me.ComboBox4
For CounterB = 1 To NoOfRowsB + 1
.AddItem Sheets("tables").Cells(RowNumB, 4)
RowNumB = RowNumB + 1
Next
End With

RowNumB = 3
NoOfRowsB = Application.CountA(Sheets("tables").Range("D3:D20" ))
With Me.ComboBox5
For CounterB = 1 To NoOfRowsB + 1
.AddItem Sheets("tables").Cells(RowNumB, 4)
RowNumB = RowNumB + 1
Next
End With

RowNumC = 3
NoOfRowsC = Application.CountA(Sheets("tables").Range("A1:A7") )
With Me.ComboBox8
For CounterC = 1 To NoOfRowsC + 1
.AddItem Sheets("tables").Cells(RowNumC, 1)
RowNumC = RowNumC + 1
Next
End With

RowNumD = 3
NoOfRowsD = Application.CountA(Sheets("tables").Range("N3:N20" ))
With Me.ComboBox9
For CounterD = 1 To NoOfRowsD + 1
.AddItem Sheets("tables").Cells(RowNumD, 14)
RowNumD = RowNumD + 1
Next
End With

End Sub
Private Sub ComboBox1_Change()
Dim r As Long
r = Me.ComboBox1.ListIndex + 3
Me.TextBox1.Value = Worksheets("Person").Cells(r, 2).Value
End Sub

Private Sub combobox3_Change()
Dim r As Long
r = Me.ComboBox3.ListIndex + 3
Me.TextBox26.Value = Worksheets("tables").Cells(r, 5).Value
End Sub

Private Sub combobox4_Change()
Dim r As Long
r = Me.ComboBox4.ListIndex + 3
Me.TextBox27.Value = Worksheets("tables").Cells(r, 5).Value
End Sub

Private Sub ComboBox5_Change()
Dim r As Long
r = Me.ComboBox5.ListIndex + 3
Me.TextBox28.Value = Worksheets("tables").Cells(r, 5).Value
End Sub

Private Sub ComboBox8_Change()
Dim r As Long
r = Me.ComboBox8.ListIndex + 3
Me.TextBox29.Value = Worksheets("tables").Cells(r, 2).Value
End Sub
Private Sub ComboBox6_Change()
Dim r As Long
r = Me.ComboBox6.ListIndex + 3
Me.TextBox29.Value = Worksheets("tables").Cells(r, 6).Value
End Sub
Private Sub ComboBox7_Change()
Dim r As Long
r = Me.ComboBox7.ListIndex + 3
Me.TextBox29.Value = Worksheets("tables").Cells(r, 6).Value
End Sub
Private Sub ComboBox9_Change()
Dim r As Long
r = Me.ComboBox9.ListIndex + 3
Me.TextBox42.Value = Worksheets("tables").Cells(r, 15).Value
End Sub


Private Sub CommandButton3_Click()
ActiveWorkbook.FollowHyperlink Address:="47"
End Sub

Private Sub CommandButton2_Click()
On Error Resume Next
Worksheets("tblPersonTable").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = TextBox1
ActiveCell.Offset(0, 4).Value = TextBox29 * 1
ActiveCell.Offset(0, 5).Value = Format(TextBox2, "mm/dd/yyyy")
ActiveCell.Offset(0, 6).Value = Format(TextBox3, "mm/dd/yyyy")
ActiveCell.Offset(0, 7).Value = TextBox42 * 1
ActiveCell.Offset(0, 8).Value = TextBox43 * 1
ActiveCell.Offset(0, 16).Value = TextBox4 * 1
ActiveCell.Offset(0, 17).Value = TextBox5
ActiveCell.Offset(0, 18).Value = ComboBox2
ActiveCell.Offset(0, 19).Value = TextBox26 * 1
ActiveCell.Offset(0, 21).Value = TextBox28 * 1
ActiveCell.Offset(0, 22).Value = TextBox6 / 100 * 1
ActiveCell.Offset(0, 23).Value = TextBox7 / 100 * 1
ActiveCell.Offset(0, 24).Value = TextBox8 / 100 * 1
ActiveCell.Offset(0, 25).Value = TextBox30
ActiveCell.Offset(0, 26).Value = TextBox9 / 100 * 1
ActiveCell.Offset(0, 27).Value = TextBox10 / 100 * 1
ActiveCell.Offset(0, 28).Value = TextBox11 / 100 * 1
ActiveCell.Offset(0, 29).Value = TextBox31
ActiveCell.Offset(0, 30).Value = TextBox46 / 100 * 1
ActiveCell.Offset(0, 31).Value = TextBox47 / 100 * 1
ActiveCell.Offset(0, 32).Value = TextBox44 / 100 * 1
ActiveCell.Offset(0, 33).Value = TextBox45
ActiveCell.Offset(0, 34).Value = TextBox35 / 100 * 1
ActiveCell.Offset(0, 35).Value = TextBox36 / 100 * 1
ActiveCell.Offset(0, 36).Value = TextBox37 / 100 * 1
ActiveCell.Offset(0, 37).Value = TextBox34
ActiveCell.Offset(0, 38).Value = TextBox41 / 100 * 1
ActiveCell.Offset(0, 39).Value = TextBox40 / 100 * 1
ActiveCell.Offset(0, 40).Value = TextBox39 / 100 * 1
ActiveCell.Offset(0, 41).Value = TextBox38
ActiveCell.Offset(0, 42).Value = TextBox15 * 1
ActiveCell.Offset(0, 43).Value = TextBox16 * 1
ActiveCell.Offset(0, 44).Value = TextBox17 * 1
ActiveCell.Offset(0, 45).Value = TextBox18 * 1
ActiveCell.Offset(0, 52).Value = CheckBox3
ActiveCell.Offset(0, 53).Value = CheckBox4
ActiveCell.Offset(0, 54).Value = TextBox24 * 1
End Sub

Private Sub label70_Click()
Worksheets("tblpersontable").Select

End Sub


All times are GMT +1. The time now is 07:38 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com