Change Event_Worksheet

[Gtasios4]

Dear All,

I need some help with my below change code event

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Range("B4").Value = "Choose Model"
End If
If Target.Address = "$B$2" Then
Range("B5").Value = "Choose extra part"
End If

End Sub

How the .value = "Choose Extra part" could be changed in the above code so all the inserted rows above the "Total bar" to give the .value = "Choose extra part"

e.g.:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
Range("B4").Value = "Choose Model"
End If
If Target.Address = "$B$2" Then
Range("B5" and inserted rows above "total" bar).Value = "Choose extra part"
End If

End Sub

 

[Gtasios4]

You can use virtually the same code as I posted:

Sub INSERTCOPY()
   lastrow = Cells(Rows.Count, "N").End(xlUp).Row
    inarr = Range(Cells(1, 14), Cells(lastrow, 14))
    For i = 5 To lastrow
     If inarr(i, 1) = "TOTAL" Then
      Exit For
     End If
    Next i
rowno = ActiveCell.Row
If rowno < i And rowno > 4 Then
With ActiveCell.EntireRow
.Copy
.Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
On Error Resume Next
.Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
Application.CutCopyMode = False
On Error GoTo 0
End With
End If
End Sub

Dear Offthelip,

I have a question, I want to have a message box before running the above code. It could be something like the below ?

Sub INSERTCOPY()
    resp = MsgBox("Are You Sure You Want To Add extra row?", vbYesNo, "Insert Extra Row")
    If resp = vbNo Then Exit Sub
   lastrow = Cells(Rows.Count, "N").End(xlUp).Row
    inarr = Range(Cells(1, 14), Cells(lastrow, 14))
    For i = 5 To lastrow
     If inarr(i, 1) = "TOTAL" Then
      Exit For
     End If
    Next i
rowno = ActiveCell.Row
If rowno < i And rowno > 4 Then
With ActiveCell.EntireRow
.Copy
.Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
On Error Resume Next
.Offset(1).SpecialCells(xlCellTypeConstants).Value = ""
Application.CutCopyMode = False
On Error GoTo 0
End With
End If
End Sub

 

[offthelip]

Looks Ok to me!

 

[Gtasios4]

Looks Ok to me!

Dear Offthelip,

Coming into that thread, I've also want to add the below change event code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B$2:K$2")) Is Nothing Then
    Select Case Range("B$2:K$2")
        Case "Macro_1": Macro1
        Case "Macro_2": Macro2
    End Select
End If
End Sub

Where in cases --> modules :

Sub Macro1()

MsgBox "Macro1"

End Sub

Sub Macro2()

MsgBox "Macro2"

End Sub

I get the below compile error:

Any idea why that's happening?

 

[offthelip]

The reason you are getting that message is because you MUST only have one Worksheet_change macro in the worksheet code and you can only have one version of it in each worksheet.
If you right click on any new blank worksheet and select "view code" . then in the window that appears select the pull down menu where it says "general" and select "worksheet" you then see in the window to right it changes to "Selectionchange" and you get this code appearing:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

now in the right hand window pull down select "Change" and this code appear in the window below:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

This is the only worksheet_change macro that will work for this sheet, any others will cause problems.

 

[Gtasios4]

The reason you are getting that message is because you MUST only have one Worksheet_change macro in the worksheet code and you can only have one version of it in each worksheet.
If you right click on any new blank worksheet and select "view code" . then in the window that appears select the pull down menu where it says "general" and select "worksheet" you then see in the window to right it changes to "Selectionchange" and you get this code appearing:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

now in the right hand window pull down select "Change" and this code appear in the window below:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

This is the only worksheet_change macro that will work for this sheet, any others will cause problems.

Dear Offthgelip,

Thank you for your help once again! I've modified my code as followed and now it works fine.

However, the msg box pops-up, only when you press again the cell while you have already selected for instance the value Macro1 (from the drop down list) in cell B$2.. I want somehow to make it once you pick a selection of a drop down list then pop-up the message..

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B$2")) Is Nothing Then
    Select Case Range("B$2")
        Case "Macro_1": Macro1
        Case "Macro_2": Macro2
    End Select
End If
End Sub

Lastly, I want to create the message box with bullet points.. how this can be done??

Like:

Sub Macro1()

resp = MsgBox("Here1", vbOKOnly, "Follow the below steps")
If resp = vbOKOnly Then Exit Sub

End Sub

 

[offthelip]

However, the msg box pops-up, only when you press again the cell

that is because you have put the code into the Worksheet_SelectionChange macro NOT the Worksheet_Change macro As I said above in the right hand box of the workhseet code window, select "Change" and that will cause the Worksheet changer code to appear like this:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

 

[Gtasios4]

that is because you have put the code into the Worksheet_SelectionChange macro NOT the Worksheet_Change macro As I said above in the right hand box of the workhseet code window, select "Change" and that will cause the Worksheet changer code to appear like this:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Got it . What about the message box of my previous question? Any help would be again much appreciated

 

[offthelip]

What about the message box of my previous question? Any help would be again much appreciated

that is a completely different question and you should start a new thread. I don't think you can format the msgbox the best I can do is:

MsgBox (" . " & "text1" & vbCr & " . " & "Text2")

 

[Gtasios4]

The reason you are getting that message is because you MUST only have one Worksheet_change macro in the worksheet code and you can only have one version of it in each worksheet.
If you right click on any new blank worksheet and select "view code" . then in the window that appears select the pull down menu where it says "general" and select "worksheet" you then see in the window to right it changes to "Selectionchange" and you get this code appearing:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

now in the right hand window pull down select "Change" and this code appear in the window below:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

This is the only worksheet_change macro that will work for this sheet, any others will cause problems.

Dear Offthelif,

Thank you once again for your useful advice! Since the below code is vital for my excel tool could you please suggest me another way to pop-up a message box when it detects the value of the drop down list in cell $B$2 ? For instance if cell B2 has the value macro1, macro2 and so on then pop-up the message..?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
    lastrow = Cells(Rows.Count, "N").End(xlUp).Row
    inarr = Range(Cells(1, 14), Cells(lastrow, 14))
    Application.EnableEvents = False   ' turn off events to avoid trigerring the worksheet change event multiple times
    Range("B4").Value = "Choose Model"
    For i = 5 To lastrow
     If inarr(i, 1) <> "TOTAL" Then
      Range(Cells(i, 2), Cells(i, 2)) = "Choose extra part"
     End If
    Next i
End If
Application.EnableEvents = True

End Sub

 

[Gtasios4]

Got it . What about the message box of my previous question? Any help would be again much appreciated

I've also tried with the below code but still get error with the range

Private Sub Worksheet_Calculate()
If Worksheets("Sheet1").Range("B2").Value = "Value1" Then
    MsgBox "Please make another selection"
End If
End Sub