Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entry in One Cell Updates Another
Firstly, thanks for any help anyone can give with this as it's doing my
head in at the moment. Cell A1 contains a value, let's say 10. Cell B1 contains an IF formula that says if user enters 10 in A1 then make cell B1 equal 20. The user enters 10 in A1 and then instantly B1 shows 20. I basically want to put this in VBA and not have a formula in B1, I can do this but only in a way where I have to run the macro to make it happen, I want it to happen instantly, the way it would happen if there were the IF formula in the spreadsheet. One last thing, the user will be able to enter a variety of codes into A1 and I want B1 (using the VBA solution) to read the code entered and display a pre-defined message in the B1 cell. (e.g user enters "Cat" into A1 and B1 then says "Cat's love fish", likewise if they enter "Dog" into A1, then B1 says "Dog's love bones", with a list of 10 or more possible combinations is it better to use Select Case for this and, if it is, could someone point me in the right direction? Many thanks! Neil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entry in One Cell Updates Another
Use a sheet change event to run when the value of cell A1 changes eg:
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ErrorHandler Application.EnableEvents = False If Target.Count = 1 And Target.Address = "$A$1" Then Select Case UCase(Target.Value) Case "DOG" Range("B1").Value = "Dogs love bones" Case "CAT" Range("B1").Value = "Cats love fish" Case 10 Range("B1").Value = 20 'other case statements here Case Else 'case else optional Range("B1").Value = "" End Select End If ErrorHandler: Application.EnableEvents = True End Sub This is worksheet event code. Right click the sheet tab, select View Code and paste the code in there. Hope this helps Rowan " wrote: Firstly, thanks for any help anyone can give with this as it's doing my head in at the moment. Cell A1 contains a value, let's say 10. Cell B1 contains an IF formula that says if user enters 10 in A1 then make cell B1 equal 20. The user enters 10 in A1 and then instantly B1 shows 20. I basically want to put this in VBA and not have a formula in B1, I can do this but only in a way where I have to run the macro to make it happen, I want it to happen instantly, the way it would happen if there were the IF formula in the spreadsheet. One last thing, the user will be able to enter a variety of codes into A1 and I want B1 (using the VBA solution) to read the code entered and display a pre-defined message in the B1 cell. (e.g user enters "Cat" into A1 and B1 then says "Cat's love fish", likewise if they enter "Dog" into A1, then B1 says "Dog's love bones", with a list of 10 or more possible combinations is it better to use Select Case for this and, if it is, could someone point me in the right direction? Many thanks! Neil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entry in One Cell Updates Another
Rowan
This really does help and completely solves this one for me - Thanks for your help and such fast response - greatly appreciated. Neil |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Entry in One Cell Updates Another
You're welcome!
"Neil10365" wrote: Rowan This really does help and completely solves this one for me - Thanks for your help and such fast response - greatly appreciated. Neil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I lock a cell after data entry so entry can not be changed | Excel Discussion (Misc queries) | |||
Input dollar amount into a cell that updates another cell and more | Excel Worksheet Functions | |||
Control Data Entry - push entry to next cell | Excel Discussion (Misc queries) | |||
restricting entry into a cell based on entry to a previous cell | New Users to Excel | |||
Cell Entry That Locks Selected Cells From Any Data Entry. | Excel Worksheet Functions |