Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
userform for updating records
I've spent two days searching for a way to use a userform to update records
from a worksheet there are plenty ways to add records using a userform but none to change\update records. My form comes up with empty textboxes & I would like it to come up with the first row of my spreadsheet with a scrollbar so I can scroll to the record I want to update. The Built in form Data Form won't work because I have Comboboxes on my form Thank you - TJ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
userform for updating records
Hi Tj
Very simple demo. Userform with Scrollbar1, Textbox1 and Commandbutton1. Active worksheet has some content in the A column. Userform code: '********* top of module ********** Option Explicit Dim RecNum As Long Dim DataSheet As Worksheet Private Sub UserForm_Initialize() Set DataSheet = ActiveSheet RecNum = 1 Me.ScrollBar1.Min = 1 Me.ScrollBar1.Max = DataSheet.Cells(DataSheet.Rows.Count, _ 1).End(xlUp).Row + 1 Me.ScrollBar1.Value = RecNum Call LoadData Me.CommandButton1.Caption = "Save" End Sub Private Sub ScrollBar1_Change() RecNum = ScrollBar1.Value Call LoadData End Sub Private Sub ScrollBar1_Scroll() RecNum = ScrollBar1.Value Call LoadData End Sub Private Sub LoadData() Me.TextBox1.Text = DataSheet.Cells(RecNum, 1).Value End Sub Private Sub CommandButton1_Click() DataSheet.Cells(RecNum, 1).Value = Me.TextBox1.Text Me.ScrollBar1.Max = DataSheet.Cells(DataSheet.Rows.Count, _ 1).End(xlUp).Row + 1 End Sub '********* end block ************* HTH. Best wishes Harald "Terry" skrev i melding ... I've spent two days searching for a way to use a userform to update records from a worksheet there are plenty ways to add records using a userform but none to change\update records. My form comes up with empty textboxes & I would like it to come up with the first row of my spreadsheet with a scrollbar so I can scroll to the record I want to update. The Built in form Data Form won't work because I have Comboboxes on my form Thank you - TJ |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
userform for updating records
Thanks Harold
Got everything but the scrollbars working TJ "Harald Staff" wrote: Hi Tj Very simple demo. Userform with Scrollbar1, Textbox1 and Commandbutton1. Active worksheet has some content in the A column. Userform code: '********* top of module ********** Option Explicit Dim RecNum As Long Dim DataSheet As Worksheet Private Sub UserForm_Initialize() Set DataSheet = ActiveSheet RecNum = 1 Me.ScrollBar1.Min = 1 Me.ScrollBar1.Max = DataSheet.Cells(DataSheet.Rows.Count, _ 1).End(xlUp).Row + 1 Me.ScrollBar1.Value = RecNum Call LoadData Me.CommandButton1.Caption = "Save" End Sub Private Sub ScrollBar1_Change() RecNum = ScrollBar1.Value Call LoadData End Sub Private Sub ScrollBar1_Scroll() RecNum = ScrollBar1.Value Call LoadData End Sub Private Sub LoadData() Me.TextBox1.Text = DataSheet.Cells(RecNum, 1).Value End Sub Private Sub CommandButton1_Click() DataSheet.Cells(RecNum, 1).Value = Me.TextBox1.Text Me.ScrollBar1.Max = DataSheet.Cells(DataSheet.Rows.Count, _ 1).End(xlUp).Row + 1 End Sub '********* end block ************* HTH. Best wishes Harald "Terry" skrev i melding ... I've spent two days searching for a way to use a userform to update records from a worksheet there are plenty ways to add records using a userform but none to change\update records. My form comes up with empty textboxes & I would like it to come up with the first row of my spreadsheet with a scrollbar so I can scroll to the record I want to update. The Built in form Data Form won't work because I have Comboboxes on my form Thank you - TJ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Updating old records | Excel Discussion (Misc queries) | |||
Updating Master Log records | Excel Worksheet Functions | |||
Unique Records Filter-Updating new entries | Excel Discussion (Misc queries) | |||
Updating Records from MS Access | Excel Discussion (Misc queries) | |||
When updating a worksheet, how do I create a link updating the sa. | Excel Worksheet Functions |