Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Updating a userform
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 177
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 8,520
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
userform for updating records Terry Excel Discussion (Misc queries) 2 August 27th 08 04:16 PM
updating userform steve Excel Programming 2 February 26th 08 06:40 PM
Userform updating via spinbutton [email protected] Excel Programming 0 September 27th 06 08:54 PM
Userform updating Richard Excel Programming 3 May 21st 04 03:37 PM
Updating .tag in Userform Control in VBA Chan[_3_] Excel Programming 1 August 20th 03 07:08 PM


All times are GMT +1. The time now is 11:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement