Worksheet_SelectionChange

[GRCArizona]

Hi - I'm somewhat new to VBA and I'm having trouble with Worksheet_SelectionChange event. I've got a Forecasting spreadsheet that has about 20 worksheets (1 'Input Sheet' and 19 other worksheets - 1 sheet for each of our 19 products). On the 'Input Sheet', I have a Data Validation drop down list in cell $B$1 for our 19 products. All Forecasting will be entered on the 'Inputs Sheet' in the range $C$4:$C$10. We will be making daily changes to our Forecast and what I'm trying to accomplish is the following: On the 'Input Sheet', user enter forecast data for 'Product 1'. Once the user is done and activates the Data Validation in cell 'Input Sheet'!$B$1', VBA should copy the info in $C$4:$C$10 to the Product 1 worksheet to the range 'Product 1'!$D$12:$D$18'. Once a new product is selected in $B$1, (IE: cell value changes from Product 1 to Product 2), the Sales information that is currently stored in 'Product 2' worksheet range $D$12:$D$18, should be brought over to the Input Sheet range $C$4:$C$10.

Is there a way to do this? Is the best way to use the _Change event?

any help is greatly appreciated,
thx, GRC

 

[p45cal]

One problem I see here is that activating the data validation cell B1 is going to happen both when you want to copy data over to your product sheet and when you want to change to another product (when you want to fetch data from a product sheet). How is the software to know which you want to do when both involve activating that cell?

I would have two buttons (activex or not doesn't matter but I have a preference to use activex buttons on a sheet (although this doesn't apply to all activex controls!)). One button would be labelled something like Fetch Data and the other would be something like Overwrite Data. Each button would have different code in its click_event (or its assigned macro if it's not an activex control) which would look at Cell B1 to determine which sheet should be used, then either copy data to or copy data from the cells in that sheet.

 

[lenze]

Hi and welcome to the board!!
First, you definitely need The WorkSheet_Change Event and I would also use the BeforeDoubleClick. Here's how it would work. In the sheet module for "Inpiut" enter this

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Address <> "$B$1" Then Exit Sub
Select Case Target
Case "Product1"
    Sheets("Input").Range("$C$4:$C$10").Copy Sheets("Product1").Range("$D$12")
Case "Product2"
    Sheets("Input").Range("$C$4:$C$10").Copy Sheets("Product2").Range("$D$12")
'etc
End Select
Sheets("Input").Range("$C$4:$C$10").ClearContents
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
Select Case Target
Case "Product1"
    Sheets("Product1").Range("$D$12:$D$18").Copy Sheets("Input").Range("$C$4")
Case "Product2"
    Sheets("Product2").Range("$D$12:$D$18").Copy Sheets("Input").Range("$C$4")
'etc
End Select
End Sub

With this in place, when the Product is changed, the info from that sheet will be transfered to the Input sheet. Now, when the forecasting is complete, a DoubleClick of B1 will send the new info back to the product sheet. It will also clear range C4:C10.
I did not test this, but I believe it will work. You, of course have to add all your products. Note the names in B1 must match "EXACTLY" the sheet names
HTH
lenze
Also: There are some other ways to do this and write the code, but this is the most staightforward for someone new to VBA to understand!

 

[GRCArizona]

Thanks for the quick response(s).

P45Cal - I thought of doing that exact thing, but I was thinking that if I set it up like this, it would leave open the possibility of one of my managers / directors inputing all of the forecasting and then hitting the wrong button and all of their work would be erased over. I was thinking of adding a msgbox warning with the End User needing to confirm their selection.


Lenze - I'm going to try to incorporate that and see if works.

Thanks again for your help, I really appreciate it. If this works, I'll be a hero at work.

GRC

 

[lenze]

I really think it is a simple way to do what you require!! If you want, you could easily add warnings to make sure the "Instructionally Challenged" don't mess it up!! See if you can get it to work and post back with any questions or if you need help adding other features or requirements.
Good Luck
lenze

 

[lenze]

I had some time and played around. It does work as expected!
I also added some messages to confirm intentions. Here is the adjusted code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Address <> "$B$1" Then Exit Sub
rspn = MsgBox("Do you wish to send the update for " & Target, vbYesNo, "Ready to send?")
If rspn = vbNo Then Exit Sub
Select Case Target
Case "Product1"
Sheets("Input").Range("$C$4:$C$10").Copy Sheets("Product1").Range("$D$12")
Case "Product2"
Sheets("Input").Range("$C$4:$C$10").Copy Sheets("Product2").Range("$D$12")
'etc
End Select
Sheets("Input").Range("$C$4:$C$10").ClearContents
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
rspn = MsgBox("Do you wish to retrieve previous forecast for " & Target, vbYesNo, "Retrieve Info?")
If rspn = vbNo Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
Select Case Target
Case "Product1"
Sheets("Product1").Range("$D$12:$D$18").Copy Sheets("Input").Range("$C$4")
Case "Product2"
Sheets("Product2").Range("$D$12:$D$18").Copy Sheets("Input").Range("$C$4")
'etc
End Select
End Sub

Cheers
lenze

 

[GRCArizona]

Lenze - thank you very much!!! Worked out great. The Msg box was a great idea as well - before adding that, i was having difficulty verifiying that I had actually sent the values over to the sheet.

Have a great weekend.

GRC

 

[GRCArizona]

Hi Lenze - can I get your thoughts on this... Where you have the code listed.


Select Case Target
Case "Product1"
Sheets("Input").Range("$C$4:$C$10").Copy Sheets("Product1").Range("$D$12")

Is there anyway to have it come over to the Product1 sheet as values? (I've got a formula in C6 and when I run the macro the formula comes over to Product1 c6 as well).

Other than that, your code works perfect.

thanks again,

GRC

 

[lenze]

Like this

Select Case Target
Case "Product1"
Sheets("Input").Range("$C$4:$C$10").Copy 
Sheets("Product1").Range("$D$12").PasteSpecial Paste:=PasteValues

What happens when you import the values from Product1 to Input. Doesn't it overwrite your formula?? You might want to adjust that code to omit importing into C6. I assume it's D14 so maybe

Select Case Target
Case "Product1"
Sheets("Product1").Range("$D$12:$D$13").Copy Sheets("Input").Range("$C$4")
Sheets("Product1").Range("$D$15:$D$18").Copy Sheets("Input").Range("$C$7")

HTH
lenze

 

[lenze]

In post 3 I alluded that this could be done differently. Here is some code that does the same thing without the Select Case and does not require you to hardcode sheet names

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Target.Address <> "$B$1" Then Exit Sub
rspn = MsgBox("Do you wish to send the update for " & Target, vbYesNo, "Ready to send?")
If rspn = vbNo Then Exit Sub
Dim mySh As String
mySh = Target
Sheets("Input").Range("$C$4:$C$10").Copy
Sheets(mySh).Range("$D$12").PasteSpecial Paste:=xlPasteValues
Sheets("Input").Range("$C$4:$C$10").ClearContents
End Sub
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
rspn = MsgBox("Do you wish to retrieve previous forecast for " & Target, vbYesNo, "Retrieve Info?")
If rspn = vbNo Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    Exit Sub
End If
Dim mySh As String
mySh = Target
Sheets(mySh).Range("$D$12:$D$13").Copy Sheets("Input").Range("$C$4")
Sheets(mySh).Range("$D$15:$D$18").Copy Sheets("InPut").Range("$C$7")
End Sub

Cheers
lenze
Note the correction in the PasteSpecial line I posted previously