Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

What's wrong with this simple 16 line code?

  Asked By: Colleen    Date: Oct 08    Category: MS Office    Views: 1224
  

In my mind, this code is simple. It waits to see what is chosen in
cell E10 of two possible choices through data validation, and then
run a procedure based on what is chosen.

Problem is, when I choose something in E10, nothing happens. Can
anyone help me?

Private Sub Workbook_SheetChange(ByVal Sheet As Object, ByVal Target
As Range)
If Target = "E10" Then
Select Case Range("E10").Value
Case "N/A"
Range("QBQuery1_1Criteria!O1:O530").Select
Selection.Copy
Range("QBQuery1_1Criteria!K1").Select
ActiveSheet.Paste
Case "All Projects Actual"
Range("QBQuery1_1Criteria!P1:P530").Select
Selection.Copy
Range("QBQuery1_1Criteria!K1").Select
ActiveSheet.Paste
End Select
End If
End Sub

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Alexis Castillo     Answered On: Oct 08

Change the Event to Workbook_SheetChange to Worksheet_Selectionchange.
Workbook_sheetchange works only when you change the worksheet. Do let me
know if that helps.

 
Answer #2    Answered By: Dot net Sachin     Answered On: Oct 08

I made the change you suggested, still nothing. Could the fact that
my code  resides in one worksheet (along with the E10 cell that I'm
selecting changes to) and the range I am copying to and from resides
in another worksheet within the same workbook?

 
Answer #3    Answered By: Renee Lane     Answered On: Oct 08

This code  needs to be in a sheet  and not a workbook. The reason for which
you are getting this error is because you have the same code in workbook
also.

 
Answer #4    Answered By: Volney Fischer     Answered On: Oct 08

I went through your code, and assumed that you have two different
worksheets:

1. The first worksheet (let's name it "Sheet1") contains the cell
(E10) which you want to check its value.

2. The second worksheet ("QBQuery1_1Criteria") contains the ranges
you are going to copy from and paste into.


Put this code  in worksheet (Sheet1) :

-----------------------------------------------------------
Private Sub Worksheet_Change(ByVal target  As Range)
If Target.Address = "$E$10" Then
With Worksheets("QBQuery1_1Criteria")
Select Case Target
Case "N/A"
.Range("O1:O530").Copy .Range("K1")
Case "All Projects Actual"
.Range("P1:P530").Copy .Range("K1")
End Select
End With
End If
End Sub
-----------------------------------------------------------


Here are my comments:

1. Change the event to (Worksheet_Change).

2. Target.Address will give the address of the changed cell, while
Target will give its value.

3. Reduce your code by using (SourceRange.Copy DestinationRange).

 
Answer #5    Answered By: Sophie Campbell     Answered On: Oct 08

I did not copy your suggestion completely. I have now.
Now I am getting a Compile error as thus with the first line  of code
highlighted in the debugger:

Compile error:

Procedure declaration does not match description of event or procedure
having the same name.

 
Answer #6    Answered By: Adalwine Fischer     Answered On: Oct 08

Ok, I've eliminated my "Procedure declaration does not match
description of event" error with this modification to the code.

Private Sub Workbook_SheetSelectionChange(ByVal Sheet As Object, ByVal
Target As Excel.Range)
If Target = "E10" Then
Select Case Range("E10").Value
Case "N/A"
Range("QBQuery1_1Criteria!O1:O530").Select
Selection.Copy
Range("QBQuery1_1Criteria!K1").Select
ActiveSheet.Paste
Case "All Projects Actual"
Range("QBQuery1_1Criteria!P1:P530").Select
Selection.Copy
Range("QBQuery1_1Criteria!K1").Select
ActiveSheet.Paste
End Select
End If
End Sub

Problem is, I'm back where I started, nothing happens when I try to
initiate this code.

 
Answer #7    Answered By: Kristin Johnston     Answered On: Oct 08

Target is a Range, comparing it with a the string "E10" only results FALSE ot
type mismatch, therefore it must be:

If Target = Range("E10") Then

 
Didn't find what you were looking for? Find more on What's wrong with this simple 16 line code? Or get search suggestion and latest updates.




Tagged: