Please help, I'm getting my butt kicked :)
I am trying very hard to make this work and I know it gotta be simple. Here is the challenge. I have a list of sales reps in sheet (salesreps!). Lets just say it rep a, b,c. Those rep names get thrown into another sheet in my workbook (monday!). Easy enough so far. Here are a few things I, for the life of me just can't figure out.
1. When I type in a rep name on (salesreps!) - after I load in his name, the cell becomes locked.
2. I'd like on the (salesreps!) page as well the opportunity that if the rep quits, a drop downbox allowing me to choose (in-active, active).
3. The kicker....should I choose inactive for a rep on the (salesreps!) sheet, the corrosponding line in (monday!) gets automatically hidden - OR - displays "Sales Rep A - Inactive"
4. How do you copy the data in a cell into another cell so that when you delete the first cell that data that was copied is not lost.
Possible? I have tried searching for answers for two days to no avail.
| Attachment | Size |
|---|---|
| sales tracker.xlsm | 521.32 KB |
More questions
I have some questions for each of your points:
1. When I type in a rep name on (salesreps!) are you saying you do not want your cells locked or you do? Currently it looks to be locking some cells. Which cells would you like locked?
2.This seems to already exisit
3. This hides your sales rep row if they are inactive. Replace the sub "ActivateSaleReps" in module mSalesreps with this code (keep in mind this only affects sheet "Monday"):
Sub ActivateSalesReps()
Dim oWs As Worksheet
Dim lngLR As Long
Dim X
Dim MDay As Worksheet
Set MDay = Sheets("Monday")
Set oWs = Sheets("SalesReps")
With oWs
.ScrollArea = ""
.Unprotect
lngLR = .Cells(Application.Rows.Count, 2).End(xlUp).Row
For X = 4 To 53
If .Cells(X, 2) <> vbNullString Then
.Rows(X & ":" & X).Locked = True
If .Cells(X, 3) = "InActive" Then
HideRep (X)
MDay.Rows(X + 2).Hidden = True
End If
If .Cells(X, 3) = "Active" Then
MDay.Rows(X + 2).Hidden = False
End If
End If
Next
.ScrollArea = strSA
End With
End Sub
4. I need and example of this, I am not sure what you are referring to.
Workbook needed
Hello!
I think I can help you out, it sounds like you have some VBA needs here. If you could attached the file in a separate post I can give it a look and let you know what I find.
Sincerely,
-Max
thanks again
sorry to get back a little late, what you did for me was great. thanks for your help!