Linking tasks to Profiles within a Macro

Status
Not open for further replies.

NickYOW

New Member
Joined
Mar 5, 2021
Messages
21
Office Version
  1. 2010
Platform
  1. Windows
I am trying to link a set of task to a Profile, my code is currently working but if I add a new task then the the reference cells such as B8(sweep floor) now becomes B9, thought the correct Macro will get called when I change B9 to add, my code within is no longer working.. as B9 relates to wash floor and B22 is linked to take out garbage, but if i add a line then the task become B10 B23 will take out garbage.

working code to call the macro
If ActiveCell = "Add" Then
Run Cells(2, ActiveCell.Column).Value & Range("A" & ActiveCell.Row).Value & "add"
End IF


working code as long as not new tasks are added.

Sub managersweepadd()

If Range("B8").Value = "Add" Then 'cell under column Manager row sweep floor changes to Add
Range("B8").Value = "Selected" ' active cell for Manager Wash change to selected
Range("B9, "B22") = "Optional" ' additional tasks as an option change from blank to Optional
selected = MsgBox("Do you wish to add the Selected jobs.", vbYesNo, "REQUIED OPTIONS")
If selected = vbYes Then
Range("B8").Value = "Added" ' change cell from Selected to Added
Worksheets("Jobs").Range("B:B").Copy Worksheets("backup").Range("B:B") ' creates a runing back up
Range("B8").Select ' returns to last active cell
extras = MsgBox("Do you want to add the Optional Jobs(s)", vbYesNo, "REQUIED OPTIONS")
If extras = vbYes Then
Range("B9,B22") = "Added" ' change option task from optional to Added
Worksheets("jobs").Range("B:B").Copy Worksheets("backup").Range("B:B") ' creates a back up
Range("B8").Select ' returns to last active cell
Else
Worksheets("Backup").Range("B:B").Copy Worksheets("Jobs").Range("B:B") ' if no selected in msg box then return all cell back to original selection but add the first selection
Range("B8").Select
End If
Else
Worksheets("Backup").Range("B:B").Copy Worksheets("Jobs").Range("B:B") ' if no is selected then return every thing back to original selection
Range("B8").Select
End If
End If

my though is to have another sheet with all tasks and positions and the relationships.
1615673426730.png


I was thing about a code with a look up function to cross reference second sheet, so what is selected will find what required to the left will display REQUIRED on the master in the correct cells and the optional ones to the right will display OPTIONAL in the corrected cells on the master, this way I can update the sheet as new tasks are introduced and make changes to what might be required or optional for each of the Positions (ie warehouse, cashier, supervisors A/Man Manager).

I need a way to keep it easy to update as new tasked are introduced.

Thanks in advance, any recommendation and/or assistance is much appreciated.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,790
Office Version
  1. 365
Platform
  1. Windows
Duplicate to: VBA Vlookup

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,130,045
Messages
5,639,746
Members
417,108
Latest member
Thein Than

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
Top