0

I am having excel sheet . which is having the field for country and state field when I am changing the country value from united state to canada the state field should be cleared . I have done this using below code for single row But I am not able to do it for multiple rows with same format could you please guide me for the same 

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$3" Then
Cells(3, 26).Value = "Please select"
End If

End Sub

Screenshot

BigBen
  • 46,229
  • 7
  • 24
  • 40
Sai
  • 389
  • 6
  • 18

2 Answers2

0
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row > 2 and Target.Row < 10 Then
   If Target.Column = 4 And Target.Cells.Count = 1 then
      ' User just changed the country
      Target.Offset(0,-1).Value = "Please select"
      Target.Offset(0,-2).Value = ""
      Target.Offset(0,-3).Value = ""
   End If
End If

End Sub

Enter you own values for Min and Max Rows (in place of the 2 and 10 I used)

Tin Bum
  • 1,397
  • 1
  • 8
  • 16
  • Great, be grateful if you would upvote/mark it as solved – Tin Bum Feb 25 '19 at 20:24
  • When i copy paste the value I am getting error message that type mismatch error 13 . Could you please help me how can i resolve it – Sai Feb 26 '19 at 14:18
  • Copy & Paste what value ? can you explain a bit more - which line of code is it crashing on ? This error arises when data type clash (eg adding a lettter to a number or similar – Tin Bum Feb 26 '19 at 14:34
  • I am getting this error. When i am copying entire row no 2 to row no 3. – Sai Feb 26 '19 at 15:07
  • May i know do we need to add the below stmt Application.EnableEvents = False – Sai Feb 26 '19 at 15:21
  • You do not NEED to alter the EnableEvents property. Whether you wish to suppress all events within any block of code is a matter for the developer to decide for each particular situation. In this code the cells being changed are not in column 4, if they were I would definitely suppress event firing before making the changes – Tin Bum Feb 26 '19 at 15:57
  • Ok I got it .randomly when i am copying the value from one row to another row I am getting the run time error 13. May i know is there anyway to handle it – Sai Feb 26 '19 at 16:00
  • try this https://stackoverflow.com/questions/8885506/excel-vba-run-time-error-13-type-mismatch – Tin Bum Feb 26 '19 at 17:26
  • I have just made one amendment ... by adding to line 4 of the answer with ... And Target.Cells.Count = 1 ensuring only 1 cell selected not a range beginning in column 4 – Tin Bum Feb 26 '19 at 17:46
  • Hi, Could you please help me with the error code . will it be resolved if i use If Not Application.Intersect(Target, Rng) Is Nothing Then – Sai Feb 27 '19 at 11:25
  • The code "If Target.Column = 4" provides the same protection you do not need Application.Intersect(Target, Rng) and since you do not have a Rng variable in my answer that will cause you problems anyway. Nothing in the above answer is going to cause your error. That is a different problem. I suggest you post that as a different question and provide a sample set of data and show what you were copying where and then we can address that – Tin Bum Feb 27 '19 at 13:02
  • Post a comment back here with a link to your new question if you wish so we'll be able to follow it up easily – Tin Bum Feb 27 '19 at 13:04
  • Thank you so much for your timely reply. I will close this as completed and i will raise as a new question – Sai Feb 27 '19 at 13:46
0

Please try the code below instead of what you have.

Private Sub Worksheet_Change(ByVal Target As Range)

    Const FirstDataRow As Long = 2          ' modify as appropriate
    Const CtryClm As String = "E"           ' modify as appropriate
    Const StateClm As String = "D"          ' modify as appropriate

    Dim Rng As Range
    Dim Msg As String

    Set Rng = Range(Cells(FirstDataRow, CtryClm), _
                    Cells(Rows.Count, CtryClm).End(xlUp))
    With Target
        If .Cells.CountLarge = 1 Then
            If Not Application.Intersect(Target, Rng) Is Nothing Then
                If Len(.Value) Then Msg = "Please select"
                Application.EnableEvents = False
                Cells(.Row, StateClm).Value = Msg
                Application.EnableEvents = True
            End If
        End If
    End With
End Sub

The code will respond to a change in any single cell (not when you paste multiple cells at once) and enter "Please select" only if the entry in the CountryColumn isn't "".

Variatus
  • 14,293
  • 2
  • 14
  • 30