Select Case Help

[davidhall]

Cell D9 varies based on inputs from another cell. The value in cell D9 could be 1, 2, or 3.

If D9 is a 1, then range p510 should be copied to cell n5
If D9 is a 2, then range q5:q15 should be copied to cell n5

N5 is the beginning of a designated range for a combo box (form control) and I would like this items within the combo box to automatically update based on the value in D9.

So, I'm having trouble with my code. See below. Any help is appreciated.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iTarget As Range
iTarget = Range("D9").Value

Application.ScreenUpdating = False

Select Case iTarget

Case 1
Range("P5:P15").Select
Selection.Copy
Range("N5").Select
ActiveSheet.Paste
Range("D3").Activate
Exit Sub

Case 2
Range("Q5:Q15").Select
Selection.Copy
Range("n5").Select
ActiveSheet.Paste
Range("D3").Activate
Exit Sub


End Select


Application.ScreenUpdating = True


End Sub


Any suggestions?

 

[Weaver]

don't dim iTarget as a range. It should probably be integer

Not that it should affect whether the code works or not but you can abbrviate

Range("P5:P15").Select
Selection.Copy
Range("N5").Select
ActiveSheet.Paste
Range("D3").Activate

to

Range("P5:P15").copy range("N5")

 

[JackDanIce]

Your code, I think makes a mistake on these lines:

Dim iTarget As Range
iTarget = Range("D9").Value

Because you're defining iTarget as a Range and then trying to assign a value to it. You can only assign the same data type to the equivalent variable

Instead, try (change in red):

Option Explicit
 
Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim iTarget As Integer
iTarget = Range("D9").Value
 
Application.ScreenUpdating = False
 
Select Case iTarget

    Case 1
      Range("P5:P15").Copy
      Range("N5").Paste
      Range("D3").Activate
      Exit Sub
 
    Case 2
      Range("Q5:Q15").Copy
      Range("N5").Paste
      Range("D3").Activate
      Exit Sub
 
End Select

Application.ScreenUpdating = True

End Sub

 

[davidhall]

Thanks. Do you guys know why my document is automatically closing out of excel entirely?

 

[JackDanIce]

No

 

[davidhall]

Ok. Here is what I have in Sheet1.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 Then

Call myMacro

End If

End Sub


Here is what I have in Module1

Sub myMacro()
Application.ScreenUpdating = False

'Dim iTarget As Integer
'iTarget = Range("D9")

If Range("D9").Value = 1 Then
Range("P5:P15").Select
Selection.Copy
Range("N5").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ElseIf Range("D9").Value = 2 Then


Range("Q5:Q15").Select
Selection.Copy
Range("N5").Select
ActiveSheet.Paste
Application.CutCopyMode = False

ElseIf Range("D9").Value = 3 Then

Range("R5:R15").Select
Selection.Copy
Range("N5").Select
ActiveSheet.Paste
Application.CutCopyMode = False

End If




Application.ScreenUpdating = True
Range("D3").Activate
End Sub


Here is the issue I am dealing with.
I can run the macro "myMacro" and it works fine. What I am trying to accomplish is if the value in D9 changes, then the macro is called. Right now, I have to click on cell D9 and press enter or something then the macro runs. The problem is that users wont have the ability to click on D9 because it will be locked. D9 contains a formula with an "if" statement linked to a variable input cell (C5) the user has the option to change. Based on the value input in C5, D9 changes and the macro should runs. It's not doing that right now and can't figure it out.


Any help?