| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.Rang
Quill,
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) 0 Then Exit Sub Application.EnableEvents = False If Target.Address = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
Thanks Bernie.
By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) 0 Then Exit Sub Application.EnableEvents = False If Target.Address = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
Quill,
In one word: practice. Do a defined, complicated project for yourself. Work on the problems here, and answer as many as you can. Read and understand the replies to those that you cannot answer. But you cannot choose to become an MVP - you are chosen to receive the award. Bernie MS Excel MVP " wrote in message ... Thanks Bernie. By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) 0 Then Exit Sub Application.EnableEvents = False If Target.Address = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
That is good advice.
Going back to the private sub worksheet_change, lets say: How do I use the VBA, When Cell A1 (trigger) is not blank, then Cell C3 will have "Enter someting" or in my case the formula "=VLOOKUP(A1,Calendar!A:B,2,FALSE)" And it has to able to list automatically downward say from line 1 to line 65,000. Say: Cell A45 is 0 then C45 will list the fomula (if Cell A45 is blank, Cell C45 is also empty) * I use another sheet named Calendar! to group the days into month, say 08-Jan-07 or 09 Jan-07 = 31-Jan-07 and when Cell A1 (trigger) is blank, there is nothing, or just empty or another pre-defined formula (IF function). "Cell C:C is fully automate in a way", triggered by column A:A and same row. I am doing it the conventional way of listing it or using IsBlank + Vlookup + lots of formula, and using the pivot table to generate the reports and copying the formulas downwards if the data line exceeds the formula lines. Thanks very much for your time. "Bernie Deitrick" wrote: Quill, In one word: practice. Do a defined, complicated project for yourself. Work on the problems here, and answer as many as you can. Read and understand the replies to those that you cannot answer. But you cannot choose to become an MVP - you are chosen to receive the award. Bernie MS Excel MVP " wrote in message ... Thanks Bernie. By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) 0 Then Exit Sub Application.EnableEvents = False If Target.Address = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
I tried this, but there was an error. Please help.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) 0 Then Exit Sub Application.EnableEvents = False If Target.Address = "$L$6" Then Target.Value = "=YEAR(TODAY())" If Target.Address = "$L$10" Then Target.Value = "187863U" If Target.Address = "$L$12" Then Target.Value = "=RIGHT(INDEX('31'!L:L,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0)),SEARCH(" - ",INDEX('31'!L:L,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0)))+1)" If Target.Address = "$L$14" Then Target.Value = "=LEFT(INDEX('31'!L:L,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0)),SEARCH(" - ",INDEX('31'!L:L,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0)))-1)" If Target.Address = "$L$18" Then Target.Value = "=INDEX('31'!J:J,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0))" If Target.Address = "$L$8" Then Target.Value = "=SUMIF('31'!S:S,CONCATENATE(G6," - ",L6),'31'!H:H)" Application.EnableEvents = True End Sub ------------------------------------------------------------------------------ " wrote: That is good advice. Going back to the private sub worksheet_change, lets say: How do I use the VBA, When Cell A1 (trigger) is not blank, then Cell C3 will have "Enter someting" or in my case the formula "=VLOOKUP(A1,Calendar!A:B,2,FALSE)" And it has to able to list automatically downward say from line 1 to line 65,000. Say: Cell A45 is 0 then C45 will list the fomula (if Cell A45 is blank, Cell C45 is also empty) * I use another sheet named Calendar! to group the days into month, say 08-Jan-07 or 09 Jan-07 = 31-Jan-07 and when Cell A1 (trigger) is blank, there is nothing, or just empty or another pre-defined formula (IF function). "Cell C:C is fully automate in a way", triggered by column A:A and same row. I am doing it the conventional way of listing it or using IsBlank + Vlookup + lots of formula, and using the pivot table to generate the reports and copying the formulas downwards if the data line exceeds the formula lines. Thanks very much for your time. "Bernie Deitrick" wrote: Quill, In one word: practice. Do a defined, complicated project for yourself. Work on the problems here, and answer as many as you can. Read and understand the replies to those that you cannot answer. But you cannot choose to become an MVP - you are chosen to receive the award. Bernie MS Excel MVP " wrote in message ... Thanks Bernie. By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) 0 Then Exit Sub Application.EnableEvents = False If Target.Address = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fixed Text-Private Sub Worksheet_Change(ByVal Target As Excel.
Quill,
You need to set the .Formula property of the target cell: see below. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) 0 Then Exit Sub Application.EnableEvents = False If Target.Address = "$L$6" Then Target.Formula= "=YEAR(TODAY())" If Target.Address = "$L$10" Then Target.Value = "187863U" If Target.Address = "$L$12" Then Target.Formula= "=RIGHT(INDEX('31'!L:L,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0)),SEARCH(" - ",INDEX('31'!L:L,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0)))+1)" If Target.Address = "$L$14" Then Target.Formula = "=LEFT(INDEX('31'!L:L,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0)),SEARCH(" - ",INDEX('31'!L:L,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0)))-1)" If Target.Address = "$L$18" Then Target.Formula= "=INDEX('31'!J:J,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0))" If Target.Address = "$L$8" Then Target.Formula= "=SUMIF('31'!S:S,CONCATENATE(G6," - ",L6),'31'!H:H)" Application.EnableEvents = True End Sub " wrote in message ... I tried this, but there was an error. Please help. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) 0 Then Exit Sub Application.EnableEvents = False If Target.Address = "$L$6" Then Target.Value = "=YEAR(TODAY())" If Target.Address = "$L$10" Then Target.Value = "187863U" If Target.Address = "$L$12" Then Target.Value = "=RIGHT(INDEX('31'!L:L,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0)),SEARCH(" - ",INDEX('31'!L:L,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0)))+1)" If Target.Address = "$L$14" Then Target.Value = "=LEFT(INDEX('31'!L:L,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0)),SEARCH(" - ",INDEX('31'!L:L,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0)))-1)" If Target.Address = "$L$18" Then Target.Value = "=INDEX('31'!J:J,MATCH(CONCATENATE(G6," - ",L6),'31'!S:S,0))" If Target.Address = "$L$8" Then Target.Value = "=SUMIF('31'!S:S,CONCATENATE(G6," - ",L6),'31'!H:H)" Application.EnableEvents = True End Sub ------------------------------------------------------------------------------ " wrote: That is good advice. Going back to the private sub worksheet_change, lets say: How do I use the VBA, When Cell A1 (trigger) is not blank, then Cell C3 will have "Enter someting" or in my case the formula "=VLOOKUP(A1,Calendar!A:B,2,FALSE)" And it has to able to list automatically downward say from line 1 to line 65,000. Say: Cell A45 is 0 then C45 will list the fomula (if Cell A45 is blank, Cell C45 is also empty) * I use another sheet named Calendar! to group the days into month, say 08-Jan-07 or 09 Jan-07 = 31-Jan-07 and when Cell A1 (trigger) is blank, there is nothing, or just empty or another pre-defined formula (IF function). "Cell C:C is fully automate in a way", triggered by column A:A and same row. I am doing it the conventional way of listing it or using IsBlank + Vlookup + lots of formula, and using the pivot table to generate the reports and copying the formulas downwards if the data line exceeds the formula lines. Thanks very much for your time. "Bernie Deitrick" wrote: Quill, In one word: practice. Do a defined, complicated project for yourself. Work on the problems here, and answer as many as you can. Read and understand the replies to those that you cannot answer. But you cannot choose to become an MVP - you are chosen to receive the award. Bernie MS Excel MVP " wrote in message ... Thanks Bernie. By the way, I have one question what is the first thing to do before becoming a MVP or some sort of VBA programmer. "Bernie Deitrick" wrote: Quill, Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Cells.Count 1 Then Exit Sub If Len(Target.Value) 0 Then Exit Sub Application.EnableEvents = False If Target.Address = "$B$5" Then Target.Value = "Enter Something" If Target.Address = "$B$6" Then Target.Value = "Enter New Code" If Target.Address = "$C$1" Then Target.Value = "Enter Old Code" If Target.Address = "$C$2" Then Target.Value = "Enter Old Code" 'And so on...... ' I hope you can see the logic Application.EnableEvents = True End Sub HTH, Bernie MS Excel MVP " wrote in message ... I have attached below and the result in Cell B5=Enter Something Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Address = "$B$5" Then If Len(Target.Value) = 0 Then Target.Value = "Enter Something" End If End Sub Question: In Cell B6 can I use some kind of formula resulting in "Enter New Code" along with the same formula above. Meaning: Same worksheet: B5=Enter Something B6=Enter New Code Additional: Example of Result Same worksheet: B5=Enter Something B6=Enter New Code C1 to C2=Enter Old Code E1=Help Me. Thanks a bundle. Quill666 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Control Toolbox and Private Sub Worksheet_Change(ByVal Target As R | Excel Discussion (Misc queries) | |||
| Private Sub Worksheet_Change(ByVal Target As Excel.Range) | Excel Programming | |||
| Private Sub Worksheet_Change(ByVal Target As Excel.Range) | Excel Worksheet Functions | |||
| Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming | |||
| Private Sub Worksheet_Change(ByVal Target As Range) | Excel Programming | |||
Hybrid Mode
