DaveGib
09-06-2017, 08:26 AM
Hi All......
Can someone please help me with my problem? - I have a worksheet that is set out as a form for a user to fill in.
Near the top of the form there is a validation list that the user must select a code from.
On a seperate hidden sheet I have several ranges of data, each range has its own code which I have defined with a named range that corresponds to the code in the validation list.
The object is that depending on the code selected by the user, the form is populated with a range that corresponds to the code.
I can get the macro to run if I hard code the Input code, but I cannot get it to run if I use a variable.
Thanks in advance
Dave
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim sc As Range '** a variable to hold the contents of J4
' ** Check if Cell J4 is updated
If Not Intersect(Target, Range("J4")) Is Nothing Then
Set sc = Range("J4")
Sheet3.Visible = xlSheetVisible '** The hidden sheet with the ranges on
Sheet3.Select
' [H1N].Select '** IF I HARD CODE THE DETAILS IN J4 IT WORKS - H1N is a code in the list selected by the user
[" & sc & "].Select '** THIS FAILS HERE - ASKING FOR AN OBJECT
Selection.Copy
Sheet10.Select
Range("A11").PasteSpecial xlPasteValues
End If
Application.ScreenUpdating = True
Can someone please help me with my problem? - I have a worksheet that is set out as a form for a user to fill in.
Near the top of the form there is a validation list that the user must select a code from.
On a seperate hidden sheet I have several ranges of data, each range has its own code which I have defined with a named range that corresponds to the code in the validation list.
The object is that depending on the code selected by the user, the form is populated with a range that corresponds to the code.
I can get the macro to run if I hard code the Input code, but I cannot get it to run if I use a variable.
Thanks in advance
Dave
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim sc As Range '** a variable to hold the contents of J4
' ** Check if Cell J4 is updated
If Not Intersect(Target, Range("J4")) Is Nothing Then
Set sc = Range("J4")
Sheet3.Visible = xlSheetVisible '** The hidden sheet with the ranges on
Sheet3.Select
' [H1N].Select '** IF I HARD CODE THE DETAILS IN J4 IT WORKS - H1N is a code in the list selected by the user
[" & sc & "].Select '** THIS FAILS HERE - ASKING FOR AN OBJECT
Selection.Copy
Sheet10.Select
Range("A11").PasteSpecial xlPasteValues
End If
Application.ScreenUpdating = True