| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with selection(?)
Using Excel 2007 - that needs to be compatible with 2003. The problem that I'm having working with appearance. With the macro list below - I'm still haiving problems with copies cells being outlined - when this isn't the desired result. The desire result is to perform the copy and not have any ranges outlined and have the cursor on the effective date. Just as if they simply moved the cursor from name to date (which will occur the majority of the time) There are four input criteria Product name Effective date State and Form number The user can either enter the product name or the form number and the program needs to automatically change the other. Effective Date and State are conditional inputs (depending on the form number) Right now the code is as follows Sub Worksheet_Change(ByVal Target As Range) Dim dur As Integer Dim Inpdate As Date Inpdate = Range("InpEDate").Value dur = Year(Now()) - Year(Inpdate) If Month(Now()) < Month(Inpdate) Then dur = dur - 1 Else If Month(Now()) = Month(Inpdate) And Day(Now()) < Day(Inpdate) Then dur = dur - 1 End If End If Application.EnableEvents = False 'Application.ScreenUpdating = False If Target.Address = Range("InpICForm").Address Then Range("c17").Copy Range("InpProd").PasteSpecial xlPasteValues ' Application.CutCopyMode = False ' Range("InpEDate").Select End If If Target.Address = Range("InpProd").Address Then Range("c18").Copy Range("InpICForm").PasteSpecial xlPasteValues ' Application.CutCopyMode = False ' Range("InpEDate").Select End If Application.EnableEvents = True Application.ScreenUpdating = True Application.CutCopyMode = False .. .. .. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with selection(?)
if its only values you need If Target.Address = Range("InpICForm").Address Then Range("InpProd").Value = Range("c17").Value End If If Target.Address = Range("InpProd").Address Then Range("InpICForm").Value = Range("c18").Value End If OR SELECT CASE Target.Address CASE Range("InpICForm").Address Range("InpProd").Value = Range("c17").Value CASE Range("InpProd").Address Range("InpICForm").Value = Range("c18").Value END SELECT "Brad" wrote: Using Excel 2007 - that needs to be compatible with 2003. The problem that I'm having working with appearance. With the macro list below - I'm still haiving problems with copies cells being outlined - when this isn't the desired result. The desire result is to perform the copy and not have any ranges outlined and have the cursor on the effective date. Just as if they simply moved the cursor from name to date (which will occur the majority of the time) There are four input criteria Product name Effective date State and Form number The user can either enter the product name or the form number and the program needs to automatically change the other. Effective Date and State are conditional inputs (depending on the form number) Right now the code is as follows Sub Worksheet_Change(ByVal Target As Range) Dim dur As Integer Dim Inpdate As Date Inpdate = Range("InpEDate").Value dur = Year(Now()) - Year(Inpdate) If Month(Now()) < Month(Inpdate) Then dur = dur - 1 Else If Month(Now()) = Month(Inpdate) And Day(Now()) < Day(Inpdate) Then dur = dur - 1 End If End If Application.EnableEvents = False 'Application.ScreenUpdating = False If Target.Address = Range("InpICForm").Address Then Range("c17").Copy Range("InpProd").PasteSpecial xlPasteValues ' Application.CutCopyMode = False ' Range("InpEDate").Select End If If Target.Address = Range("InpProd").Address Then Range("c18").Copy Range("InpICForm").PasteSpecial xlPasteValues ' Application.CutCopyMode = False ' Range("InpEDate").Select End If Application.EnableEvents = True Application.ScreenUpdating = True Application.CutCopyMode = False . . . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with selection(?)
Your suggestion still produces a "box" around the range "InpProd" once the
down arrow or the mouse is used. (This assumes that the user changes the product name) -- Wag more, bark less "Patrick Molloy" wrote: if its only values you need If Target.Address = Range("InpICForm").Address Then Range("InpProd").Value = Range("c17").Value End If If Target.Address = Range("InpProd").Address Then Range("InpICForm").Value = Range("c18").Value End If OR SELECT CASE Target.Address CASE Range("InpICForm").Address Range("InpProd").Value = Range("c17").Value CASE Range("InpProd").Address Range("InpICForm").Value = Range("c18").Value END SELECT "Brad" wrote: Using Excel 2007 - that needs to be compatible with 2003. The problem that I'm having working with appearance. With the macro list below - I'm still haiving problems with copies cells being outlined - when this isn't the desired result. The desire result is to perform the copy and not have any ranges outlined and have the cursor on the effective date. Just as if they simply moved the cursor from name to date (which will occur the majority of the time) There are four input criteria Product name Effective date State and Form number The user can either enter the product name or the form number and the program needs to automatically change the other. Effective Date and State are conditional inputs (depending on the form number) Right now the code is as follows Sub Worksheet_Change(ByVal Target As Range) Dim dur As Integer Dim Inpdate As Date Inpdate = Range("InpEDate").Value dur = Year(Now()) - Year(Inpdate) If Month(Now()) < Month(Inpdate) Then dur = dur - 1 Else If Month(Now()) = Month(Inpdate) And Day(Now()) < Day(Inpdate) Then dur = dur - 1 End If End If Application.EnableEvents = False 'Application.ScreenUpdating = False If Target.Address = Range("InpICForm").Address Then Range("c17").Copy Range("InpProd").PasteSpecial xlPasteValues ' Application.CutCopyMode = False ' Range("InpEDate").Select End If If Target.Address = Range("InpProd").Address Then Range("c18").Copy Range("InpICForm").PasteSpecial xlPasteValues ' Application.CutCopyMode = False ' Range("InpEDate").Select End If Application.EnableEvents = True Application.ScreenUpdating = True Application.CutCopyMode = False . . . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with selection(?)
the "boxis because the cell is selected
Range("InpEDate").Select if you don't need this line, then delete it (there are two in your code, none in mine) A "feature" of excel is that the currently selected cell is highlighted. you can get rid of thios by freezing the sheet and selectig a cell out of sight, like AZ1000 with A1 showing in the top left "Brad" wrote: Your suggestion still produces a "box" around the range "InpProd" once the down arrow or the mouse is used. (This assumes that the user changes the product name) -- Wag more, bark less "Patrick Molloy" wrote: if its only values you need If Target.Address = Range("InpICForm").Address Then Range("InpProd").Value = Range("c17").Value End If If Target.Address = Range("InpProd").Address Then Range("InpICForm").Value = Range("c18").Value End If OR SELECT CASE Target.Address CASE Range("InpICForm").Address Range("InpProd").Value = Range("c17").Value CASE Range("InpProd").Address Range("InpICForm").Value = Range("c18").Value END SELECT "Brad" wrote: Using Excel 2007 - that needs to be compatible with 2003. The problem that I'm having working with appearance. With the macro list below - I'm still haiving problems with copies cells being outlined - when this isn't the desired result. The desire result is to perform the copy and not have any ranges outlined and have the cursor on the effective date. Just as if they simply moved the cursor from name to date (which will occur the majority of the time) There are four input criteria Product name Effective date State and Form number The user can either enter the product name or the form number and the program needs to automatically change the other. Effective Date and State are conditional inputs (depending on the form number) Right now the code is as follows Sub Worksheet_Change(ByVal Target As Range) Dim dur As Integer Dim Inpdate As Date Inpdate = Range("InpEDate").Value dur = Year(Now()) - Year(Inpdate) If Month(Now()) < Month(Inpdate) Then dur = dur - 1 Else If Month(Now()) = Month(Inpdate) And Day(Now()) < Day(Inpdate) Then dur = dur - 1 End If End If Application.EnableEvents = False 'Application.ScreenUpdating = False If Target.Address = Range("InpICForm").Address Then Range("c17").Copy Range("InpProd").PasteSpecial xlPasteValues ' Application.CutCopyMode = False ' Range("InpEDate").Select End If If Target.Address = Range("InpProd").Address Then Range("c18").Copy Range("InpICForm").PasteSpecial xlPasteValues ' Application.CutCopyMode = False ' Range("InpEDate").Select End If Application.EnableEvents = True Application.ScreenUpdating = True Application.CutCopyMode = False . . . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with selection(?)
I'd agree with you but this is the new code
Application.EnableEvents = False If Target.Address = Range("InpICForm").Address Then Range("InpProd").Value = Range("c17").Value End If If Target.Address = Range("InpProd").Address Then Range("InpICForm").Value = Range("c18").Value End If Application.EnableEvents = True Application.ScreenUpdating = True and I'm still getting the box.... -- Wag more, bark less "Patrick Molloy" wrote: the "boxis because the cell is selected Range("InpEDate").Select if you don't need this line, then delete it (there are two in your code, none in mine) A "feature" of excel is that the currently selected cell is highlighted. you can get rid of thios by freezing the sheet and selectig a cell out of sight, like AZ1000 with A1 showing in the top left "Brad" wrote: Your suggestion still produces a "box" around the range "InpProd" once the down arrow or the mouse is used. (This assumes that the user changes the product name) -- Wag more, bark less "Patrick Molloy" wrote: if its only values you need If Target.Address = Range("InpICForm").Address Then Range("InpProd").Value = Range("c17").Value End If If Target.Address = Range("InpProd").Address Then Range("InpICForm").Value = Range("c18").Value End If OR SELECT CASE Target.Address CASE Range("InpICForm").Address Range("InpProd").Value = Range("c17").Value CASE Range("InpProd").Address Range("InpICForm").Value = Range("c18").Value END SELECT "Brad" wrote: Using Excel 2007 - that needs to be compatible with 2003. The problem that I'm having working with appearance. With the macro list below - I'm still haiving problems with copies cells being outlined - when this isn't the desired result. The desire result is to perform the copy and not have any ranges outlined and have the cursor on the effective date. Just as if they simply moved the cursor from name to date (which will occur the majority of the time) There are four input criteria Product name Effective date State and Form number The user can either enter the product name or the form number and the program needs to automatically change the other. Effective Date and State are conditional inputs (depending on the form number) Right now the code is as follows Sub Worksheet_Change(ByVal Target As Range) Dim dur As Integer Dim Inpdate As Date Inpdate = Range("InpEDate").Value dur = Year(Now()) - Year(Inpdate) If Month(Now()) < Month(Inpdate) Then dur = dur - 1 Else If Month(Now()) = Month(Inpdate) And Day(Now()) < Day(Inpdate) Then dur = dur - 1 End If End If Application.EnableEvents = False 'Application.ScreenUpdating = False If Target.Address = Range("InpICForm").Address Then Range("c17").Copy Range("InpProd").PasteSpecial xlPasteValues ' Application.CutCopyMode = False ' Range("InpEDate").Select End If If Target.Address = Range("InpProd").Address Then Range("c18").Copy Range("InpICForm").PasteSpecial xlPasteValues ' Application.CutCopyMode = False ' Range("InpEDate").Select End If Application.EnableEvents = True Application.ScreenUpdating = True Application.CutCopyMode = False . . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with selection(?)
the 'box' presumably is the normal excel indicator of which cell is selected.
The fact that you'rr using 'Target' suggests that you've used either the selection_change or the change event - in either case, the cell will be highlighted by the 'box' ... thats just normal excel. As I indicated, this 'box' will always be there unless you freeze the pane and select a cell thats 'off screen' "Brad" wrote: I'd agree with you but this is the new code Application.EnableEvents = False If Target.Address = Range("InpICForm").Address Then Range("InpProd").Value = Range("c17").Value End If If Target.Address = Range("InpProd").Address Then Range("InpICForm").Value = Range("c18").Value End If Application.EnableEvents = True Application.ScreenUpdating = True and I'm still getting the box.... -- Wag more, bark less "Patrick Molloy" wrote: the "boxis because the cell is selected Range("InpEDate").Select if you don't need this line, then delete it (there are two in your code, none in mine) A "feature" of excel is that the currently selected cell is highlighted. you can get rid of thios by freezing the sheet and selectig a cell out of sight, like AZ1000 with A1 showing in the top left "Brad" wrote: Your suggestion still produces a "box" around the range "InpProd" once the down arrow or the mouse is used. (This assumes that the user changes the product name) -- Wag more, bark less "Patrick Molloy" wrote: if its only values you need If Target.Address = Range("InpICForm").Address Then Range("InpProd").Value = Range("c17").Value End If If Target.Address = Range("InpProd").Address Then Range("InpICForm").Value = Range("c18").Value End If OR SELECT CASE Target.Address CASE Range("InpICForm").Address Range("InpProd").Value = Range("c17").Value CASE Range("InpProd").Address Range("InpICForm").Value = Range("c18").Value END SELECT "Brad" wrote: Using Excel 2007 - that needs to be compatible with 2003. The problem that I'm having working with appearance. With the macro list below - I'm still haiving problems with copies cells being outlined - when this isn't the desired result. The desire result is to perform the copy and not have any ranges outlined and have the cursor on the effective date. Just as if they simply moved the cursor from name to date (which will occur the majority of the time) There are four input criteria Product name Effective date State and Form number The user can either enter the product name or the form number and the program needs to automatically change the other. Effective Date and State are conditional inputs (depending on the form number) Right now the code is as follows Sub Worksheet_Change(ByVal Target As Range) Dim dur As Integer Dim Inpdate As Date Inpdate = Range("InpEDate").Value dur = Year(Now()) - Year(Inpdate) If Month(Now()) < Month(Inpdate) Then dur = dur - 1 Else If Month(Now()) = Month(Inpdate) And Day(Now()) < Day(Inpdate) Then dur = dur - 1 End If End If Application.EnableEvents = False 'Application.ScreenUpdating = False If Target.Address = Range("InpICForm").Address Then Range("c17").Copy Range("InpProd").PasteSpecial xlPasteValues ' Application.CutCopyMode = False ' Range("InpEDate").Select End If If Target.Address = Range("InpProd").Address Then Range("c18").Copy Range("InpICForm").PasteSpecial xlPasteValues ' Application.CutCopyMode = False ' Range("InpEDate").Select End If Application.EnableEvents = True Application.ScreenUpdating = True Application.CutCopyMode = False . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Limiting selection in a cell AND linking that selection to a list | Excel Discussion (Misc queries) | |||
| Change InputBox Range Selection to Column Letter Selection | Excel Programming | |||
| Copy Selection - Paste Selection - Delete Selection | Excel Programming | |||
| Copy Selection - Transpose Selection - Delete Selection | Excel Discussion (Misc queries) | |||
| Combo Box selection only shows bound column info after selection made. | Excel Programming | |||
Linear Mode
