Make sure user is within certain rows before code executes

[daniels012]

I have created code to enter data from the Active cell. However, I would like to edit it so that the user can only make this work if they are on Rows 6 through Row 29.

Here is the code:

Sub DropDown7_Change()
    'Right here I want: If ActiveCell. is in Row 6-29 Then 
    Application.ActiveCell.Value = Range("D3").Value
    Application.ActiveCell.Offset(, 1).Value = Range("F3").Value
   Else Msg = "Move into the proper rows"
End Sub

How can I do a check before the code is executed to be sure the users active cell is in Rows 6-29.

let me know if I need to provide more info!!
Thank you,
Michael

 

[daniels012]

Vog,
That worked!
Is there a way though to allow the execution of the code to continue with a yes or know?

Sometimes I have 2 guys with the name Bob Davis therefore I want to be able to let the code execute normally. So having a yes or no response would take the code further.

Thank you so much though you have really helped alot!!!

Michael

 

[VoG]

Try this - there is a bit of repetition but at least it should be clear when you come to look at the code in a year's time

Sub DropDown7_Change()
Dim Found As Range, Response As Integer
If ActiveCell.Row >= 6 And ActiveCell.Row <= 29 Then
    Set Found = Range("A6:Z29").Find(what:=Range("D3").Value)
    If Not Found Is Nothing Then
        Response = MsgBox(prompt:=Range("D3").Value & " already copied - copy again?", Buttons:=vbYesNo)
        If Response = vbYes Then
            ActiveCell.Value = Range("D3").Value
            ActiveCell.Offset(, 1).Value = Range("F3").Value
        Else
            MsgBox "Duplicate copy cancelled"
        End If
    Else
        ActiveCell.Value = Range("D3").Value
        ActiveCell.Offset(, 1).Value = Range("F3").Value
    End If
Else
    MsgBox "Move into the proper rows"
End If
End Sub

 

[daniels012]

Vog II,
Thank you so very much. This works like a charm.
I appreciate your time.
One day I might figure out this coding stuff!!!!

Michael

 

[daniels012]

VogII,
Not sure if you'll look back on this old post but if you can't maybe someone else can help!!

I want to apply this code to a control toolbox combo box instead of the forms combo box. It doesn't quite work the same because:
The cursor moves from the active cell to the combo box and I am sure there may be other things to effect it as well.

Thank You,
Michael

 

[daniels012]

bump

 

[daniels012]

bump

 

[daniels012]

What is happening, is that the message box "Move into the proper rows" pops up twice.
Not sure why? It does enter the data into the active cell, but it does not move the data from the ActiveCell.Offset

Any ideas?

Michael

 

[VoG]

Sorry Michael but I don't know what's wrong and without knowing what else is going on in the workbook it is practically impossible for me to diagnose.

Could you (perhaps rename the sub) and attach it to a button - so the user would make a selection then click a 'Go' button or whatever.

I apologise for the delay in responding - I was asleep whilst you were 'bumping' last night (my time) and I've been out since 5am today.

 

[daniels012]

no prolbem!!
I figured it out. Without going into a lot of details... I had the cells ir chose from the lists had a formula that zeroed them out when the name was chosen. Well, everytime there was a zero it would give me the message because of the multiple zeros.

Thank you though!!!!!
Michael