Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 193
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,327
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 193
Default 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
Search this Thread:

Advanced Search
Display Modes

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
Updating old records law Excel Discussion (Misc queries) 0 December 2nd 07 04:01 PM
Updating Master Log records Sinner Excel Worksheet Functions 7 June 18th 07 01:39 PM
Unique Records Filter-Updating new entries Jim C Excel Discussion (Misc queries) 1 August 1st 06 04:18 PM
Updating Records from MS Access Andrew Thacker Excel Discussion (Misc queries) 1 March 27th 06 08:10 AM
When updating a worksheet, how do I create a link updating the sa. Phlashh Excel Worksheet Functions 9 January 27th 05 07:05 PM


All times are GMT +1. The time now is 01:45 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"