Help with my code for userform dumping to multiple spreadsheets

[kellem80]

Hi,
I have a userform, which currently works partially as needed (I have included the code for the form below)
I need the form to also perform another task, I need it to send data to one more worksheet within the workbook.
I am unsure how to handle this in the code to ensure information goes to both worksheets. I also need the information for the additional worksheet to be ADDED to a particular cell based on the information provided in the form.

What I need:
1. I need the Value from "txtAmt" to be entered in a specific cell on different worksheet ("TR Data")
2. the specific cell needs to be in the same row as the number selected as value for "cboTR", specific cell would be in column H of that row
3. In a perfect world, I would like it added as a formula to the current amount in column H, but will settle for net result of adding the value to the current value in that cell
I will also need to execute the protect and unprotect on "TR Data" worksheet the same as being done on "TR Increase Log"
Any help advice is greatly appreciated!

Private Sub cmbTR_Change()
End Sub

Private Sub cmdAddIncr_Click()
Dim iRow as Long
Dim ws as Worksheet
Set ws = Worksheets ("TR Increase Log")

'find first empty row in database
iRow = ws.cells(Rows.Count,1) _
.end(xlUp).Offset(1,0).Row

'unprotect sheet
ws.Unprotect "XXX"

'remind completing TR number
IF Trim(Me.cboTR.Value) = "" then
ME.cboTR.SetFocus
msgBox "Enter TR Number!!"
Exit Sub
End If

'copy the data to the log
ws.cells (iRow, 1).Value = Me.cboTR.Value
ws.cells (iRow, 2).Value = Me.TxtDate.Value
ws.cells (iRow, 3).Value = Me.txtAmt.Value
ws.cells (iRow, 4).Value = Me.txtBA.Value
ws.cells (iRow, 5).Value = Me.txtcomment.Value

Me.cboTR.SetFocus

'clear data
Me.cboTR.Value = ""

'protect sheet
ws.Protect "XXX"

End Sub

Private Sub Cmd2_Click()
Unload Me
End Sub

Private Sub Userform_Initialize()
 Dim CTR As Range
 Dim ws as Worksheet
Set ws = Worksheets ("TR Data")

For Each cTR In ws.Range ("TR_Number")
With ME.cboTR
.AddItem cTR.Value
End With

Next cTR
Me.TxtDate.Value = Format(Date, "Medium Date")
Me.sboTR.SetFocus
End Sub

 

[kellem80]

OK, I have figured out how to send the information to multiple sheets. What I am still completely lost on is how to:

based the choice selected in cboTR, how to ADD the txtAmt.Value to the current value in column G of the worksheet in the same row as the as the choice the use selects for cboTR

Example:

on the worksheet in row 5 column D, "#123456" (which is unique---) is located and currently in row 5 Column G it is showing $5,000

User completes form:

User selects #123456 under the cboTR box (this is populated by a range which is Column D of the worksheet)
User enters $10,000 into the txtAmt box


I want the form to find row 5 based on the users choice of #123456 and ADD $10,000 to the current $5,000

IS this even possible????



My current code (and limited knowledge) only has me placing info in the next empty row---i need help getting past this and getting the below to do the above!

'find first empty row in database
iRow = ws.cells(Rows.Count,1) _
.end(xlUp).Offset(1,0).Row

'unprotect sheet
ws.Unprotect "XXX"

'copy the data to the log
ws.cells (iRow, 3).Value = Me.txtAmt.Value
Me.cboTR.SetFocus

'protect sheet
ws.Protect "XXX"

End Sub

 

[Warship]

Does this do it?
(I'm not entirely clear which sheet gets the txtAmt added to it)

ws.Cells(cboTR.ListIndex + 1, "G") = ws.Cells(cboTR.ListIndex + 1, "G") + txtAmt.Value

 

[kellem80]

Thanks,
I tried adding the line you provided and after manipulting a couple of items, it is almost working. only problem it is adding the amount to the line above (so if the number I choose is in row 6, it is adding the txtAmt to row 5 in column G.
I've included the whole command for the dump, in case the issue is somewhere else in my code:

Private Sub Cmd2_Click()
Dim ws as Worksheet
set ws = Worksheets ("TR Data")

'unprotect Sheet
ws.Unprotect "XXX"

'remind for completing TR number

If Trim(Me.cboTR.Value) = "" Then
Me.cboTR.SetFocus
MsgBox "Enter TR Number!!"
Exit Sub
End If

'Copy data to database
ws.cells(cboTr.ListIndex + 1, "G") = ws.Cells(cboTR.ListIndex + 1, "G") +txtAmt.Value

'Clear the data
Me.cboTR.Value = ""

'protect sheet
ws.Protect "XXX"
 Unload Me
End Sub

 

[Norie]

Sounds like the column that populates the combobox has a header.

Add 2 instead of 1 to the ListIndex to take care of that.

 

[kellem80]

Forget it, got it. it works!!! I needed to account for the header row on the receiving sheet!! changed the +1 to +2


THANKS FOR YOUR HELP!!!!

YAY!