Make sure user is within certain rows before code executes

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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:
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try

Code:
Sub DropDown7_Change()
    If ActiveCell.Row >= 6 And ActiveCell.Row <= 29 Then
    '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 If
End Sub
 
Upvote 0
Vog II,
Thank you for your response. This seems to work (sort of? :eek: )

If I am not on the rows the message box does indeed change, but the Active cell does not change and obviously the code does not execute, but the message box does not come up?

Not sure if it matters, but my combobox is from the Forms toolbar.
Michael
 
Upvote 0
Ah, well that code doesn't actually display a message but this does (I've also deleted the unnecessary Application. in front of ActiveCell).

Code:
Sub DropDown7_Change()
    If ActiveCell.Row >= 6 And ActiveCell.Row <= 29 Then
        ActiveCell.Value = Range("D3").Value
        ActiveCell.Offset(, 1).Value = Range("F3").Value
    Else
        MsgBox "Move into the proper rows"
    End If
End Sub

I'm not sure what you mean about the activecell not changing :eek:
 
Upvote 0
This uses a MsgBox and does not update the Msg variable, but it does seem to work?


Sub DropDown7_Change()
Dim lngMyR&

lngMyR = ActiveCell.Row

If Not (lngMyR >= 6 And lngMyR <= 29) Then
MsgBox "Move into the proper rows!"
Else

Application.ActiveCell.Value = Range("D3").Value
Application.ActiveCell.Offset(, 1).Value = Range("F3").Value
End If
End Sub
 
Upvote 0
OK All that worked!!! :) :)

let me ask this, is there any code I can add to my code that will not let a duplicate in the rows 6 through 29? :confused: :confused:

An example might be I have Bob Davis (Cell D3) being copied to the Active Cell using the code I have provided, but before it puts that name in there is there code i can check to see if any cell in Rows 6 through 29 have Bob Davis?
:eek: :eek:
If it does; stop code and give message "That name is already used" Do you want to put it in the cell anyway and continue with the code (Yes button) or do you want to put another name (No button) and cancel code.

Thank You for all the help so far!!!!
Michael
 
Upvote 0
See if you can adapt this code fragment (I've arbitrarily assumed Z as the maximum column)

Code:
Dim Found As Range
Set Found = Range("A6:Z29").Find(what:=Range("D3").Value)
If Found Is Nothing Then
    MsgBox "OK to copy"
    'replace this with the copy routine
Else
MsgBox "Dup!!"
End If
 
Upvote 0
Maybe something like:
If Count Range("D3").value in Range.Rows(6:29)>0 Then

Just not sure!!

Michael
 
Upvote 0
Whilst you're doing that, if you wanted to use Count then you would need something like

Code:
If WorksheetFunction.Count(Range("A6:Z29"), Range("D3").Value) > 0 Then
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top