Data Validation with "VBA Refreshing" out of order
 

Dear All,
I am adjusting an excel.sample, discovered in this beautiful website
http://www.contextures.com/excelfiles.html to my purpose for job. The
spreadsheet
sample is: http://www.contextures.com/excelfile...#DataValNameID , but I
realize that it doesn't work or I should say, it works only in the dedicated
cells, built by the author (B2:B16) and not in a different range (such as
I6:I16). I analized the VBA routine, but I didn't see anything strange,
perhaps I missed something.

Can anyone help me in refreshing the Data Validation in another cells range?
Thanks in advance,
Marc


°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°° °°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°
The routine is as follow:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If Target.Cells.Count 1 Then GoTo exitHandler
If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub

Sub MyFix()
Application.EnableEvents = True

End Sub

Data Validation with "VBA Refreshing" out of order
 

I'm glad you like the site! You can change the number of the target
column, e.g.:

If Target.Column = 9 Then

so it works on column I, instead of column B.


Marc wrote:
Dear All,
I am adjusting an excel.sample, discovered in this beautiful website
http://www.contextures.com/excelfiles.html to my purpose for job. The
spreadsheet
sample is: http://www.contextures.com/excelfile...#DataValNameID , but I
realize that it doesn't work or I should say, it works only in the dedicated
cells, built by the author (B2:B16) and not in a different range (such as
I6:I16). I analized the VBA routine, but I didn't see anything strange,
perhaps I missed something.

Can anyone help me in refreshing the Data Validation in another cells range?
Thanks in advance,
Marc


°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°° °°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°°
The routine is as follow:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If Target.Cells.Count 1 Then GoTo exitHandler
If Target.Column = 2 Then
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub

Sub MyFix()
Application.EnableEvents = True

End Sub







--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Data Validation with "VBA Refreshing" out of order
 

"Debra Dalgleish" wrote
I'm glad you like the site! You can change the number of the target
column, e.g.:

If Target.Column = 9 Then

so it works on column I, instead of column B.


--
Debra Dalgleish


*Thanks a lot* Debra, it works perfectly! :-)

If I had 2 or more that one data validation cells in the same Sheet1, what
should I write in the VBA code in order to refreshing both cells? I tried to
adjust the vba row as follow:

If Target.Column = 2 and 9 Then

but only the first column refreshs.
Anyway thank you very much for your first answer and best wishes!
Marc

Data Validation with "VBA Refreshing" out of order
 

If you want the same code to run for either column, you could use:

If Target.Column = 2 Or Target.Column = 9 Then

If you want different code to run in specific columns, you could use:

'======================
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If Target.Cells.Count 1 Then GoTo exitHandler
Select Case Target.Column
Case 2, 9
If Target.Value = "" Then GoTo exitHandler
Application.EnableEvents = False
Target.Value = Worksheets("Codes").Range("C1") _
.Offset(Application.WorksheetFunction _
.Match(Target.Value, Worksheets("Codes") _
.Range("ProdList"), 0), 0)
Case 8
If Target.Value = "" Then GoTo exitHandler
MsgBox "Column 8"
Case Else
If Target.Value = "" Then GoTo exitHandler
MsgBox "Nothing happens here"
End Select

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub
'====================


Marc wrote:
"Debra Dalgleish" wrote

I'm glad you like the site! You can change the number of the target
column, e.g.:

If Target.Column = 9 Then

so it works on column I, instead of column B.


--
Debra Dalgleish



*Thanks a lot* Debra, it works perfectly! :-)

If I had 2 or more that one data validation cells in the same Sheet1, what
should I write in the VBA code in order to refreshing both cells? I tried to
adjust the vba row as follow:

If Target.Column = 2 and 9 Then

but only the first column refreshs.
Anyway thank you very much for your first answer and best wishes!
Marc




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Data Validation with "VBA Refreshing" out of order
 

*Thanks a lot* Debra, it works perfectly! :-)

If I had 2 or more that one data validation cells in the same Sheet1, what
should I write in the VBA code in order to refreshing both cells? I tried
to adjust the vba row as follow:

If Target.Column = 2 and 9 Then

but only the first column refreshs.
Anyway thank you very much for your first answer and best wishes!
Marc

Delete my last post Debra, I solved the problem by myself. In the vba row we
must use "Or" and NOT "And", so both data validation can refreshing.
e.g.
If Target.Column = 2 Or 9 Then

Kind regards,
Marc

Data Validation with "VBA Refreshing" out of order
 

"Debra Dalgleish" wrote...
If you want the same code to run for either column, you could use:

If Target.Column = 2 Or Target.Column = 9 Then

If you want different code to run in specific columns, you could use:

'======================
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler
If Target.Cells.Count 1 Then GoTo exitHandler
Select Case Target.Column
Case 2, 9
If Target.Value = "" Then GoTo exitHandler
[...]

Thanks again for your prompt replay, your guidelines are useful for building
a spreadsheet in the factory concernig a ExpenditureNote template for the
reimbursement fuel cost when associates use their own car and not the
company ones.
Kind regards,
Marc
Italy