Enter a number in two Cells simultaneously, One as +ve & one as -ve

[vikrampnz]

I have a spreadsheet (“Account”) with columns assigned to each person e.g. Rick, Tom, Angela, Sue . The names are mentioned in the top row from C1 to F1 as headers. I am trying to have two comboboxes to select “TO” and “FROM” names from the same list showing above names and the amount that needs to be transferred from one person to another.

For example, From: “TOM” , to: “Angela” , Amount: 100 (To be entered in a text box) and the row should show “-100” in the last emply cell in column TOM and “100” in the last emply row in column ANGELA.

I am trying to find a VB code to do this. Can someone help! Your help would be much appreciated!

Thanks

 

[My Aswer Is This]

You said:

the worksheet is opened

I can provide a script to do this when the "Worksheet is activated.

Workbooks are "Opened
Worksheets are "Activated"

 

[My Aswer Is This]

Right click on your sheet tab and paste in this script:

Private Sub Worksheet_Activate()
Dim i As Long
Lastcolumn = Cells(7, Columns.Count).End(xlToLeft).Column
    
    
    Into.Clear
    OutOf.Clear
    
    For i = 3 To Lastcolumn
    Into.AddItem Cells(7, i).Value
    OutOf.AddItem Cells(7, i).Value
    
    Next
End Sub

This script will run every time you "Activate" the sheet.
Activate means when you click on the sheet tab and can see the sheet.

 

[Russell Hauf]

I'm really glad that you've got a good solution to your problem. But I can't help saying that this seems like it could be solved so easily if you set up your data in a different manner. Maybe without any VBA at all. Just for kicks, here's what I would probably do:

1. Create a sheet called Users. Enter in all of your users. Personally, I'd create a table for this.
2. You should only need say 4 columns for your transaction data: FROM, TO, AMOUNT, TRAN_CODE. You could even use Data Validation for for the names on this sheet. It might look something like this (I also have this data set up as a Table, and I've used Data Validation for the names - let us know if you'd like to see more. I'd be willing to try to upload the simple workbook or email it):

Excel 2013
	A	B	C	D
1	TRAN_CODE	TRAN_AMOUNT	FROM	TO
2	TR001	100	PETER	BOB
3	TR002	50	MARY	TIM
Transactions

You can still get the info you want by person by filtering or querying your data.

Anyway, my 2 cents. I'll add that it is my opinion that the majority of the harder questions on this forum (and all Excel forums, for that matter) could be much more easily solved with a restructure of the data. Ok, off soapbox.

The great part of all of this is that you've learned some new stuff! Happy Excelling,

 

[My Aswer Is This]

I modified this script to put in some error checking.

If the user fails to choose a name from either combobox or fails to enter a value in the Textbox he will get a Message Box and the script will stop:

Private Sub CommandButton2_Click()
'Install script in Command Button to perform you script
Dim c As Range
Dim Lastrow As Long
If TextBox1.Value = "" Or Into.Value = "" Or OutOf.Value = "" Then MsgBox "You Missing something": Exit Sub
Lastcolumn = Cells(7, Columns.Count).End(xlToLeft).Column
   For Each c In Range(Cells(7, 3), Cells(7, Lastcolumn))
    
            
        If c.Value = Into.Value Then
            Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    
            Cells(Lastrow, c.Column).Value = TextBox1.Value
        End If
    Next
    
           For Each c In Range(Cells(7, 3), Cells(7, Lastcolumn))
        
        If c.Value = OutOf.Value Then
            Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
            Cells(Lastrow, 1).Value = "TCO " & Lastrow - 7
            Cells(Lastrow, c.Column).Value = "-" & TextBox1.Value
        End If
    Next
    'Clear ComboBoxes and Textbox
    TextBox1.Value = ""
    Into.Clear
    OutOf.Clear
    
End Sub

 

[vikrampnz]

Thanks a lot "My Answer Is This" ! I will try the modified codes and let you know !

Thanks a lot Russell for your feedback and suggestions!