Microsoft Office Forums

Go Back   Microsoft Office Forums > >
Reload this Page Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon
Register FAQ Mark Forums Read

Reply
Page 4 of 4 123 4
 
Thread Tools Display Modes
  #1  
Old 11-30-2018, 04:29 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 64bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2010 64bit
Expert
  
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,920
Pecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond reputePecoflyer has a reputation beyond repute
Default

Marcia
click your UserCP. In the left column you have " Pictures and albums". You can add your pictures there ( Frankly I never used it, so any tips are welcome ) or create album(s)
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #2  
Old 11-30-2018, 04:37 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2007
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 551
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
Quote:
Originally Posted by Pecoflyer View Post
Marcia
click your UserCP. In the left column you have " Pictures and albums". You can add your pictures there ( Frankly I never used it, so any tips are welcome ) or create album(s)

Thanks. I tried opening this forum in my cellphone where the pics are stored but I couldn't find the box where I need to enter the password. There's only the username in it.
Reply With Quote
  #3  
Old 10-15-2019, 07:31 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2013
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 551
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
Hi all. The teachers who are the users of the attached code, courtesy of this Forum, have been happily using it for more than a year. Now, they are requesting for enhancements as follows:
1. That "Sick" be added to differentiate it from an attendance record that is just plain truant absent. "Sick" should be colored green.
2. In the totals column (Col AC), Sick should be treated as absent for half day (.5) or whole day (1).
3. In AH92 and AI92, the total incidence of at least 5 consecutive absences should now include in the formula the count of at least 5 consecutive absences due to illness.
4. After the update, the diagonal lines should be invisible for a neater look.
This is a repost but number 3 and 4 are added.
Thank you.
Attached Files
File Type: xlsm FORM.SY 2019-2020 - Copy.xlsm (297.5 KB, 7 views)
Reply With Quote
  #4  
Old 10-16-2019, 06:59 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2019
Expert
  
Join Date: Apr 2014
Posts: 948
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
Unfortunately I had already done most of the work to update the code before you attached your file in the last message, so the work was done on the old file. I attach that file and leave it to you to do the remaining adjustments.


I have added a bunch (60 ish) dictionary entries - you'll have to compare these with your own and choose what to keep.
Note that I've addded two columns and changed "" to Empty and "TRUE" to True. This is more robust.



Regarding the diagonal lines in cells, this is used (partly) to determine which cells to process so I haven't deleted them, only changed their colour. I restore their visibility with the Reset button. This is so that users can see that a given cell could be a candidate for processing. The code only changes the colour, not the thickness/dottedness etc. so you can choose those aspects manually independently.


In the code line that colours the green triangles I've included a range of shades of green for you to experiment with:
Code:
myshape.Fill.ForeColor.RGB = 31261  '5296274,3969910,10213316,5287936,31261,vbGreen    '(different greens to try)
I've done nothing about any formulae on the sheet at all.
Attached Files
File Type: xlsm msofficeforums40597JUNE 2018 QUERYBoffer03.xlsm (62.7 KB, 7 views)
Reply With Quote
  #5  
Old 10-17-2019, 04:55 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2013
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 551
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
Wonderful. I changed the "S" to "K" due to the formulae in rows 44 and 74 that use "SI" for shifted in learners.
"Sick" absences should also be included in the below code that count the number of learners with more than 5 consecutive absences. Please help again,
Code:
Function CountOfRowsWithNOrMoreConsecutiveXs(myRange, N)
Vals = myRange.Value
For rw = 1 To UBound(Vals)
  ThisRowCount = 0
  For colm = 1 To UBound(Vals, 2)
    If InStr(1, UCase(Vals(rw, colm)), "X") > 0 Then ThisRowCount = ThisRowCount + 1 Else ThisRowCount = 0
    If ThisRowCount >= N Then
      RowCount = RowCount + 1
      Exit For
    End If
  Next colm
Next rw
CountOfRowsWithNOrMoreConsecutiveXs = RowCount
End Function
Thank you.
Attached Files
File Type: xlsm FORM.SY 2019-2020 - Copy.xlsm (144.8 KB, 7 views)
Reply With Quote
  #6  
Old 10-17-2019, 05:26 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2019
Expert
  
Join Date: Apr 2014
Posts: 948
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
Untested:
Code:
If (InStr(1, UCase(Vals(rw, colm)), "X") > 0 Or InStr(1, UCase(Vals(rw, colm)), "K") > 0) Then ThisRowCount = ThisRowCount + 1 Else ThisRowCount = 0
Reply With Quote
  #7  
Old 10-17-2019, 02:29 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2013
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 551
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
Quote:
Originally Posted by p45cal View Post
Untested:
Code:
If (InStr(1, UCase(Vals(rw, colm)), "X") > 0 Or InStr(1, UCase(Vals(rw, colm)), "K") > 0) Then ThisRowCount = ThisRowCount + 1 Else ThisRowCount = 0
Perfect. Thank you.
Reply With Quote
  #8  
Old 10-17-2019, 10:48 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2013
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 551
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
I am now "packaging" the form for dissemination to the ultimate users. The Form is 95% formula so I tried to lock the sheet except for the input cells $D$13:$AA$42, $D$44:$AA$73 and D$10 but the debugger led me to:
Code:
 cll.Errors(xlUnlockedFormulaCells).Ignore = True
Is there any other way of unlocking the input cells?
Thank you.
Reply With Quote
  #9  
Old 10-18-2019, 02:26 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2019
Expert
  
Join Date: Apr 2014
Posts: 948
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
What is text of the error report?
Send me your file as it stands.
Reply With Quote
  #10  
Old 10-18-2019, 03:08 AM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2013
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 551
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
Okay, file attached.
Attached Images
File Type: png 2019-10-19 18_02_20-Microsoft Visual Basic.png (10.8 KB, 9 views)
Attached Files
File Type: xlsm FORM.SY 2019-2020 - Copy.xlsm (144.8 KB, 7 views)
Reply With Quote
  #11  
Old 10-18-2019, 08:28 AM
p45cal's Avatar
p45cal p45cal is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 10 Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2019
Expert
  
Join Date: Apr 2014
Posts: 948
p45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond reputep45cal has a reputation beyond repute
Default
There's more than just that problem with protected sheet; to solve the 2 that I came across:

1. Add the following line at the start of both ResetMe and blah:
Code:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, UserInterfaceOnly:=True
2. remove the line
Code:
cll.Errors(xlUnlockedFormulaCells).Ignore = True
from both macros (it seems the green error triangles that Excel puts in are only visible when the sheet is unprotected).




Separately, for PrintTable to work properly you need to change
Destn.Resize(, 8) = dictItem
to:
Destn.Resize(, 10) = dictItem
Reply With Quote
  #12  
Old 10-18-2019, 04:01 PM
Marcia's Avatar
Marcia Marcia is offline Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Windows 7 32bit Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Office 2013
Expert
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon 		 
Join Date: May 2018
Location: Philippines
Posts: 551
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default
Just perfect.... Thank you. What a big leap from shading triangles with crayons and computing sums and averages by punching the calculator to "just click".
Reply With Quote
Reply
Page 4 of 4 123 4

« Previous Thread | Next Thread »
Thread Tools
Display Modes
Hybrid Mode Hybrid Mode


Similar Threads
Thread Thread Starter Forum Replies Last Post
New learner need help - convert tables in word format to ppt slides. sas Word VBA 5 09-21-2017 08:33 PM
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Good morning/afternoon/evening based on time dwirony Word VBA 1 01-03-2017 01:01 PM
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Need conditional formatting formula namedujour Excel 1 05-27-2016 08:58 AM
Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon Conditional Formatting Formula teza2k06 Excel 1 08-22-2014 02:49 AM
Conditional Formula UICOMP Excel 1 05-18-2012 12:59 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:08 AM.

Contact Us - Privacy Statement - Top

Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft