Using Forms to Update Worksheet Data

[RussellT]

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?

 

[Ryan H]

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.