Forum Discussion
Excel Data entry
Thanks for sharing the file. Here is your file with the code in it. Let me know if this works as desired.
Please test the attached by entering either 1 or -1 in cell B1. The solution is based on Change_Event and the following code is placed on Sheet1 Module.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
On Error GoTo Skip
If Target.Address(0, 0) = "B1" Then
Application.EnableEvents = False
If Target = 1 Or Target = -1 Then
Target = Target.Value * Range("A1").Value
End If
End If
Skip:
Application.EnableEvents = True
End Sub
Subodh_Tiwari_sktneer Thank you so much! It worked!
Now If I want to do that for a whole table? Say 22 Columns and 10 lines?
Wouldn't it be better that you upload a sample file to show the layout of your data as the solution is based on VBA and if you change the layout, it won't work correctly? After uploading the sample file, describe your requirement again based on existing data in the file and let me know what do you want to achieve with the help of cell references.
Subodh_Tiwari_sktneer You're right! So, https://docs.google.com/spreadsheets/d/1-K-pfGZ0KyAl-FaP4oBYolIrQzMFr4XpUwAjg4IhikY/edit#gid=0's a sample of a table to better explain the situation and the solution I'm looking for.
- When I enter the value 1 or -1 in any cell in the range [B1:AB1] I want that cell to show/return (1*A1) or (-1*A1).
And the same goes for the other lines:
- When I enter the value 1 or -1 in any cell in the range [B2:AB2] I want that cell to show/return (1*A2) or (-1*A2)
- and so on till line 11.
Hope you can open the link (Didn't know how to upload a file).
