<div>
I don't understand 2. Please try again.
</div>


<div>
Are you saying that if the date were 1/1 and the Replacement Staff was Employee10 that H2 on the AFL tab should be 56?
</div>


<div>
Yes that is exactly it. but now that I am thinking about it, could we add the extra hours that would be added from the shift being offered. so for example, if the shift being offered was a 7-3 shift (8 hours) then the over hours tab would be 56 + 8 = 64.
</div>


29130731.xlsm

Agency Director

Max Hersey

<div>
the hours thing works great! but I couldnt get the original staff name to auto populate
</div>


<div>
Unless I don't understand what you mean, I think it already does that.<br />
<br />
Before:<br />
<a href="https://filedb.experts-exchange.com/incoming/2018/12_w52/1408254/2018-12-27_16-28-32.png" target="_blank" title="2018-12-27_16-28-32.png (56 KB)"><img alt="2018-12-27_16-28-32.png" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2018/12_w52/800_1408254/2018-12-27_16-28-32.png" /></a>After:<br />
<a href="https://filedb.experts-exchange.com/incoming/2018/12_w52/1408253/2018-12-27_16-30-10.png" target="_blank" title="2018-12-27_16-30-10.png (58 KB)"><img alt="2018-12-27_16-30-10.png" class="file-block lazyload" style="max-width: 800px;" data-src="https://filedb.experts-exchange.com/incoming/2018/12_w52/800_1408253/2018-12-27_16-30-10.png" /></a>
</div>


2018-12-27_16-28-32.png

2018-12-27_16-30-10.png

<div>
so on january 1st 2019, the staff name ffor the 7 to 3 shift on the pinegarry tab is employee28. So what I was hoping we could do is have the original staff auto populate the name for the position. So in this case it would be employee28 in the original staff column. then, when the shift is confirmed, it would lock that row on the AFT tab so that when the replacement staff name replaces the original staff name, it wont change the original staff name on the AFT tab because the row is locked.
</div>


<div>
Other than locking, is the workbook doing what you want?
</div>


<div>
it doesnt seem to be populating the original staff name once the three variables are put in.
</div>


<div>
I don’t have the code in front of me right now but I believe it does that when Confirm is changed to “Yes”.
</div>


<div>
oh ok I was hoping the original staff would populate on the AFT tab once the three variables are filled in on the AFT tab. then once the lock happens it wont change the original name on the aft tab but it will change the name on the house tab.
</div>


<div>
I just want the AFT tab &nbsp;columns to auto populate as much as possible so that there is less work for my staff to do. Plus its a good control.
</div>


<div>
See if this is better. The Original Staff is auto populated as soon as the Replacement Staff is chosen.<br />
<a href="https://filedb.experts-exchange.com/incoming/2018/12_w52/1408292/29130731a.xlsm" target="_blank" class="file-inline" title="29130731a.xlsm (175 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>29130731a.xlsm</a>
</div>


29130731a.xlsm

<div>
It still doesn't seem to be filling in the original staff column. So for example, if the shift is a 6am to 9am on Jan 1, 2019 at maison pacific the original staff name on the AFT tab should auto populate to employee1.
</div>


<div>
All this time I thought that the only time that the original staff should auto populate is if the Replacement Staff was changed. I understand now it should also happen if the shift changes. Should it also change if the Shift Date or House changes?
</div>


<div>
It should always auto-populate based on the shift date, house, and shift time that is chosen on the AFT tab (Column B, C, and D). Based on what is in those three cells, it will pull the name from the house tabs. so on jan 1, 2019 at maison pacific for the 6 to 9am shift, &quot;Employee1&quot; would auto fill in the original staff cell because that is the name on the maison pacific tab on jan 1 for the 6 to 9am shift.
</div>


29130731b.xlsm

<div>
Yes it works well now! Is there a way for it to say open if there is not a name in the position already? For example, On Jan. 11th there is no one working the 8 to 4 shift at francis miller. Because there is not a name in that position could it say open? This is important because sometimes we have to schedule additional shifts that arent even filled.
</div>


<div>
Sure, I’ll do that tomorrow.
</div>


<div>
Awesome Thanks! I have a few other ideas to try out as well.
</div>


29130731c.xlsm

<div>
It seems to change for the 8 to 4 shifts but not the other shifts that should be open. For example, if you pick a shift that is filled like the 12 to 8 (employee30) and then click the 6 to 9 or 7 to 3 or 4 to 9 then it stays as employee30. Also, I tried to confirm the shift and it locked the row properly but the replacement staff name did not seem to fill in properly. I had the jan 11 francis miller 12 to 8 confirmed with employee11 as the replacement staff. It removed employee30 on the francis miller tab but left the position blank.
</div>


<div>
Ok, it wasn't as easy as I thought but I think it's fixed now.<br />
<br />
I made two additional changes:<br />

<ul>
<li>I sorted the list of employees that are shown in the userform. It looks wrong now but I believe when there are real names it will be in alphabetical order. If you don't want it that way then please let me know and I can easily change it back to the way it was.</li>
<li>I changed the color of the locked cells. If you don't like it then go to the 'Home' tab at the top of Excel, select the little dropdown button in the lower right-hand portion of the 'Styles' section and choose one that you like better. You could also tell me to just change it back to the way it was.</li>
</ul>
</div>


<div>
Can you add the updated file?
</div>


29130731d.xlsm

<div>
Yes this works good! I noticed that a random number goes into the original staff cell if the shift does not appear. Is this supposed to happen?
</div>


<div>
No, so please describe the situation, what happened and what should have happened.
</div>


<div>
i tried filling in a new row on the AFT tab and when u fill in the A, B, and C column, the E column generates a random number. I also just noticed that the original staff name disappeared when I enter the replacement staff name, the original staff name is removed. Also, when the replacement staff was employee11 the hourse column was -16. not too sure why this is happening
</div>


<div>
In a new row when columns A to D are filled in, what if anything should appear in E?
</div>


<div>
when D is filled in it gives either open or the employee name. but when D isnt filled in yet it gives a random number.
</div>


<div>
I've fixed he random number ib column E but in a new row where just A to D are filled in do you want to see &quot;Open&quot;?
</div>


<div>
Yea as long as that shift is not already filled. If it is filled then it should show the employee name
</div>


<div>
Try this. BTW, make note of this somewhere but shouldn't there be some restriction on when a row can be locked? Currently it can be locked without error if just the house has a value and everything else is blank.<br />
<a href="https://filedb.experts-exchange.com/incoming/2018/12_w52/1408421/29130731e.xlsm" target="_blank" class="file-inline" title="29130731e.xlsm (177 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>29130731e.xlsm</a>
</div>


29130731e.xlsm

<div>
everything else seems to be fixed except for when we pick the replacement staff, the original staff name disappears on the AFT tab
</div>


<div>
I don't see that happening, I'm looking at the AFT sheet and the original staff is 26 and the replacement staff is 11. I choose 30 for the replacement staff from the userform and now original staff is 11 and replacement staff is 30. Isn't that correct?
</div>


<div>
hmmm. maybe try filling out a second row because that is what happens when I fill one out for the first time. Also, I agree that there should be regulations so that it can be locked until all cells in that row r filled out
</div>


<div>
<blockquote>
Also, I agree that there should be regulations so that it can be locked until all cells in that row r filled out
</blockquote>
For the next or subsequent change please describe what cells are required to be filled prior to locking.
</div>


<div>
I think we should have all the cells filled out before it locks the row (A to J)
</div>


<div>
<blockquote>
maybe try filling out a second row because that is what happens when I fill one out for the first time.
</blockquote>
In the new row what columns are filled in when this happens?
</div>


<div>
all of them except for the notify column. its once I pick the replacement name that it disappears
</div>


29130731f.xlsm

<div>
yea i think that did fix it! now it is just the hour column that is still not working for me.<br />
<a href="https://filedb.experts-exchange.com/incoming/2018/12_w01/1408475/Screenshot--1-.png" target="_blank" class="file-inline" title="Screenshot--1-.png (111 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Screenshot--1-.png</a>
</div>


Screenshot--1-.png

29130731g.xlsm

<div>
Oh haha well that was silly of me.
</div>


<div>
I have a few other things that I would like to add to this workbook but I am going to make it a new question.
</div>


<div>
<div class="content wysiwyg-content">
1) Can the original staff column of the AFT tab auto populate based on the name that is already on the house tab for that specific date and shift time? Rather than it being a drop down.<br />
2) Can the over hours column on the AFT tab auto-populate the total hours (that are calculated on the hours tab) for the replacement staff for the given week that the shift falls into?<br />
<a href="https://filedb.experts-exchange.com/incoming/2018/12_w52/1408250/Schedule.xlsm" target="_blank" class="file-inline" title="Schedule.xlsm (162 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>Schedule.xlsm</a>
</div>
</div>


Schedule.xlsm

1) Can the original staff column of the AFT tab auto populate based on the name that is already on the house tab for that specific date and shift time? Rather than it being a drop down.
2) Can the over hours column on the AFT tab auto-populate the total hours (that are calculated on the hours tab) for the replacement staff for the given week that the shift falls into?

auto populate original staff name

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.