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

 

[vikrampnz]

Hello

My Comboboxes are named "cboFrom" and "cboTO"......Textbox is called "TRAmount" and the command button is called "cmdEnter". All are ActiveX controls. I can change these names easily if I have to.

Thanks

 

[My Aswer Is This]

You did not explain this:

And I'm not sure what this has to do with what we are doing.
Column A has the Transaction Code for each transaction added i.e. CT001, CT002 etc

How does this Transaction code get entered into column "A"
Do you want this as part of the script?
In my script the names start in column "C" as you previously said you wanted. What's in column "B"

So Bob's name is in column C and the values go into the rows below Bob's name
Mary's name is in column "D" and the values go in the column below Mary's name
This is what we want correct?

 

[vikrampnz]

If the Transaction Code in column A can be generated automatically (incremental) , it would be great !

I want to select a name (Lets say Peter") from combobox (cboFrom) , then select a name (Lets say "Tim" from Combobox (cboTo) and then enter amount (Lets say 100) in textbox and click the command button. The last empty row will be selected from column A for this entry. In Peter's column it will go as "-100" and in Tim's column it will go as "100". Both these entries will be in the same row, but respective columns.

Thanks

 

[vikrampnz]

There will have to be a lot of empty rows in each person's column. It will only have values where a particular transaction involves that person (either From or To). So, if Peter's entry is 15th and he does not have any involvement in the first 14 transactions, then first 14 rows in Peter's column will be empty. Same logic applies for ALL the people / respective columns.

Thanks

 

[vikrampnz]

The Transaction Code is only used to define a particular transaction and identify the last empty row (across ALL columns) for a new transaction entry.

 

[My Aswer Is This]

OK I left the Comboxs the way I described below

Please follow the steps
1. In Row 7 column "A" enter "TCO" later you can change things if you want.
2.Be sure and enter all you names into row 7 starting in Column "C" and fill across as far as you want.

I have a script here for you.
You will need two Activex Comboboxes
And two Activex Command Buttons
One of the Command Buttons will be to load your names from Row(7) into the Comboboxes
The other Command Button will be used to run your script
To make things easier for you I want you to name your two Comboboxes. "Into" and "Out of"
The Textbox where you enter your Amout should be named "Textbox1"
To name your Comboboxes right click on the Combobox and choose properties and change the name.
Click on the Command button once to load the names into the two Comboboxes.
This only ever needs to be done once unless you add more names to Row(7)
Then enter your amount into the textbox named "Textbox1"
Choose the name you want from the two comboboxes and then press the command button
You can add more names in Row(7) if you want.

Here are the scripts:


This script goes into a command button does not matter the name.
When you activate this button it loads all your names into both Comboboxes

Private Sub CommandButton1_Click()
'Install this script in Command button to load you Comboboxes with values
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 goes into a command button does not matter the name.
When you activate this button it runs your script. Be sure and select a value from each Combobox before activating this button;

Private Sub CommandButton2_Click()
'Install script in Command Button to perform you script
Dim c As Range
Dim Lastrow As Long
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
    
End Sub

See how this works and let me know please.

 

[vikrampnz]

Thanks a lot ! It worked like a dream !

One last favour, what should I add in the script so that after every transaction entry it Clears both the comboboxes and the text box.

Thanks again !


Thanks a lot

 

[My Aswer Is This]

Clearing the textbox would be easy. But if we clear the comboboxes then that would mean you would have to reload them every time. Why would you want to do that?

 

[My Aswer Is This]

This is the modified script you wanted.

See changes in red:

Private Sub CommandButton2_Click()
'Install script in Command Button to perform you script
Dim c As Range
Dim Lastrow As Long
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
    [COLOR=#ff0000]TextBox1.Value = ""
    Into.Clear
    OutOf.Clear[/COLOR]
    
End Sub

You should know these comboboxes will have to be reloaded every time the workbook is opened.

That could be done automatically every time the sheet is activated if you want that feature.

 

[vikrampnz]

Thanks for the Clear function.

Could you please ;et me know how these comboboxes could be loaded automatically everytime the worksheet is opened? only If its not going to take up too much of your time? I would really appreciate it.

Thanks again !