Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Forms to Update Worksheet Data
Thanks in advance for the feedback and sorry to those who read it in general
questions. I have VBA form that I've created to display data to the screen using the following code (code only shows of few of the columns of data being loaded to form). Public Sub LoadLongInfo3(ByVal selectedProject As String) Dim databaseRow As Long Set reportsSheet = Sheets("Reports") Set resultsSheet = Sheets("Results") Set devdataSheet = Sheets("DevData") 'Find DataRows To Be Loaded Into Form devdataSheet.Activate devdataSheet.Cells.Find(What:=selectedProject, After:=devdataSheet.Cells(1, 1), LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, MatchCase:=True).Activate databaseRow = ActiveCell.Row 'Load Data Into Form lblConcept.Caption = devdataSheet.Cells(databaseRow, 5) lblCity.Caption = devdataSheet.Cells(databaseRow, 7) lblDevName.Caption = devdataSheet.Cells(databaseRow, 2) lblMasterPlan.Caption = devdataSheet.Cells(databaseRow, 10) lblSubmarket.Caption = devdataSheet.Cells(databaseRow, 8) lblRegion.Caption = devdataSheet.Cells(databaseRow, 9) lblTBM.Caption = devdataSheet.Cells(databaseRow, 16) lblTBColRow.Caption = devdataSheet.Cells(databaseRow, 17) lblZip.Caption = devdataSheet.Cells(databaseRow, 18) lblLocation.Caption = devdataSheet.Cells(databaseRow, 19) End Sub To update the data I currently simply go to the datasheet and work my way around it changing cells as need, however there are over 150 columns on the sheet. Is there a way I can nodify this code to edit rows on the orignial source datasheet? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Forms to Update Worksheet Data
It appears you are loading data into a Userform. I took the liberty to make
your code a bit more readable by using the With Statement which is recommended if you are using the same reference for multiple lines of code. In your case it is devdataSheet. Plus when you are dealing with Labels you don't have to explicitly use the .Caption property because the .Caption property is a labels default property. Just a few notes. Public Sub LoadLongInfo3(ByVal selectedProject As String) Dim databaseRow As Long Set reportsSheet = Sheets("Reports") Set resultsSheet = Sheets("Results") Set devdatasheet = Sheets("DevData") ' Find DataRows To Be Loaded Into Form With devdatasheet .Activate .Cells.Find(What:=selectedProject, _ After:=.Cells(1, 1), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=True).Activate databaseRow = ActiveCell.Row ' Load Data Into Form lblConcept = .Cells(databaseRow, 5) lblCity = .Cells(databaseRow, 7) lblDevName = .Cells(databaseRow, 2) lblMasterPlan = .Cells(databaseRow, 10) lblSubmarket = .Cells(databaseRow, 8) lblRegion = .Cells(databaseRow, 9) lblTBM = .Cells(databaseRow, 16) lblTBColRow = .Cells(databaseRow, 17) lblZip = .Cells(databaseRow, 18) lblLocation = .Cells(databaseRow, 19) End With End Sub Now, as far as your question goes. I am assuming you are wanting to edit rows of data from your userform. If this is correct then you can use lines of code like this in your userform module. I will assume you have textboxes named Textbox1, Textbox2, etc. You simply just reverse the code you have above. Me represents the userform. With devdatasheet .Cells(databaseRow, 5) = Me.Textbox1 .Cells(databaseRow, 7) = Me.Textbox2 .Cells(databaseRow, 2) = Me.Textbox3 .Cells(databaseRow, 10) = Me.Textbox4 End With If this is not the answer to your question then reply back with more specfic details of your question and needs. Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "RussellT" wrote: Thanks in advance for the feedback and sorry to those who read it in general questions. I have VBA form that I've created to display data to the screen using the following code (code only shows of few of the columns of data being loaded to form). Public Sub LoadLongInfo3(ByVal selectedProject As String) Dim databaseRow As Long Set reportsSheet = Sheets("Reports") Set resultsSheet = Sheets("Results") Set devdataSheet = Sheets("DevData") 'Find DataRows To Be Loaded Into Form devdataSheet.Activate devdataSheet.Cells.Find(What:=selectedProject, After:=devdataSheet.Cells(1, 1), LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, MatchCase:=True).Activate databaseRow = ActiveCell.Row 'Load Data Into Form lblConcept.Caption = devdataSheet.Cells(databaseRow, 5) lblCity.Caption = devdataSheet.Cells(databaseRow, 7) lblDevName.Caption = devdataSheet.Cells(databaseRow, 2) lblMasterPlan.Caption = devdataSheet.Cells(databaseRow, 10) lblSubmarket.Caption = devdataSheet.Cells(databaseRow, 8) lblRegion.Caption = devdataSheet.Cells(databaseRow, 9) lblTBM.Caption = devdataSheet.Cells(databaseRow, 16) lblTBColRow.Caption = devdataSheet.Cells(databaseRow, 17) lblZip.Caption = devdataSheet.Cells(databaseRow, 18) lblLocation.Caption = devdataSheet.Cells(databaseRow, 19) End Sub To update the data I currently simply go to the datasheet and work my way around it changing cells as need, however there are over 150 columns on the sheet. Is there a way I can nodify this code to edit rows on the orignial source datasheet? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add/Update data stored in another worksheet | Excel Programming | |||
Excel Data Entry Forms (Data, Worksheet) | Excel Discussion (Misc queries) | |||
VBA: Update worksheet when txtBox data changes. | Excel Worksheet Functions | |||
VBA: Update worksheet when txtBox data changes. | Excel Programming | |||
VBA: Update worksheet when txtBox data changes. | Excel Worksheet Functions |