Posts by splion

I have a MACRO that sends tomorrow's schedule pairings to those involved. I have them .Dsiplay rather than send, then I click send on each one - I'm still in Beta stage. However, the recipients are getting those emsils days later... any ideas? I guess that this isn't exactly an Excel issue, but it sort of is one. I'm not posting code because it sin't the code, it is Outlook, but I don't know what to change in Outlook to be able to send emails right away.

I need to send emails with a confidential tag. Our in-house custom tag is "Patient-Confidential", there is no "Confidential" under "Sensitivity" but "Patient-Confidential" is the 3rd item on the "Confidential" drop-down list if that has anything to do with it. The default Outlook tag for confidential is .Sensitivity=3, but it doesn't change the sensitivity at all. How can I assign sensitivity, either MS Outlook "Confidential" or "Patient-Confidential"?

I've successively tried all of the following variations for assigning sensitivity:

With OutMail
.To = recipient
.Subject = strsubject
.Body = strbody
.Sensitivity = "Patient-Confidential" 'to match the sensitivity status
.Sensitivity = 3
.oLSensitivity = 3
.oLSensitivity = confidential  'and olconfidential
.oLSensitivity = sensitivity.olConfidential 
.Display
End With

I have a spreadsheet that helps to make a daily schedule for ~30 people with ~50 assignments. I've been refining it and saving the workbook from the prior day for the subsequent day's schedule and updating the personnel for the day. Three or four people work on the schedule every day, so it is shared on a Teams drive.

Some of the cells that use long formulas like Textjoin are now giving the error #VALUE. This is new. No part of the sheet related to those cells has changed in the past 6 months and it used to give the appropriate value in Teams. Now it sometimes doesn't give the appropriate value in desktop Excel, version listed below.

EDIT running: Microsoft® Excel® for Microsoft 365 MSO (Version 2110 Build 16.0.14527.20336) 32-bit

So, what should I do? Is there a shortcut to start a fresh build? Does 'autosaving' a large number of versions contribute to the problem?

I have an Excel VBA Macro that helps to create a schedule and then emails each of the ~50 people their assignment for the next day via and MS Outlook. Some of those emails are not received even though the correct address is entered. Has anyone tried anything similar? Does Outlook view the large number of emails as a virus and stop sending them? I can alter the program so that it displays each message, then I add my own email address to the cc: line and click <send> to send it manually. However, some of the emails still aren't received and I'm not receiving the email even though I'm cc'ing my gmail address. Any ideas?

Our IT department is less than helpful.

I manipulate an output from a scheduling program to form an array in the appropriate order. Then I paste that array into a Worksheet that is then used to make daily work assignments. That Worksheet contains Conditional Formatting to ensure that our 'Assignment Rules' are met.

Issue:

The damn Conditional Formatting (CF) keeps getting broken up into covering only a portion of the Worksheet that the CF is supposed to cover. For example, instead of one CF Rule covering $A$3:$E$35, it will get broken up into a number of smaller arrays instead of all of $A$3:$E$35, for example there will be two rules that are the same but "Apply To" different ranges, such as $A$3:$B$35 and $C$3:$E$35. Occasionally it also skips a portion of that total array $A$3:$E$35. Sometimes it will also break down a rule such that instead of it covering an an entire range, it will duplicate that one Rule and Apply it to individual cells. If I try to protect the cells, it won't let my VBA paste the array in the first place. I feel that I have too many CFs to write each of them into a VBA script in order to apply them via a Macro.

Is there a way to preserve Conditional Formats in VBA? Why is this happening? I'm not dragging and dropping cells throughout the Worksheet.

Would it be easier to just number the items in the SortOrderArray, find the position of each value in in my data in the SortOrderArray and write the numbers to an adjacent column, then sort the entire table based on those numbers? It wouldn't be that tedious and it would add an extra column, but I think that it would make my sort function much more robust.

I try to sort based upon a custom sort order, which is an array. I populated the custom sort order array from a range of cell values.

Why can't I just call the name of the array as below?

CustomOrder:=SortOrderArray

Each time that I run the VBA Macro, it adds another Custom List. These accumulate and I think that they cause issues. Can I delete all old custom sort lists at the start of a run?

Why do I need to use Order:=xlAscending? Does this interfere with my use of the Custom List?

Range("A2:C61").Select
    Application.DeleteCustomList ListNum:=9
    Application.AddCustomList ListArray:=Array("PUH IV", "PUH D PUH Charge", "PUH Flex Shift")
    ActiveWorkbook.Worksheets("Output2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Output2").Sort.SortFields.Add2 Key:=Range("B2:B61" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
        "PUH IV,PUH D PUH Charge,PUH Flex Shift" & _
        "", DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Output2").Sort
        .SetRange Range("A2:B61")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Thank you! I kept "Set" when I didn't need to keep it...

Set AssignmentArray= 
'I needed to use 
AssignmentArray = 

When I change the accepting variable type to variant, I get error '13' type mismatch.

I want to run a Macro in one spreadsheet that allows the user (who is not computer literate) to select an array of assignments from a spreadsheet where they are listed from A1:B27 with names in Col "A" and Assignments in Col "B".

When I run the code, I get the InputBox, which allows me highlight the range of cells run-time error '424': Object Required

Dim AssignmentArray As Variant 
Dim rng As Range 
Set rng = Application.InputBox(prompt:="Select Assignments from worksheet", Type:=64) 

AssignmentArray = rng.value 

I feel like I could debug something that was entered into rng, but since the highlighted cells aren't passed into rng I don't have anything to work through. I believe that I need to specify another object but I don't know what/how.

Many Thanks

I want to send a series of emails with commands like:

"Dave to Truck #2" on Dec 1

"Steve to Truck #3" on Dec 3

etc and accumulate them in a worksheet, ColA being the date, ColB being the Request:

Thx!

I want to send an email to a spreadsheet. Is that possible?

I want to accumulate special requests from multiple people in a spreadsheet. Can I setup a spreadsheet to do that?

I need to identify when a person in Column2 has multiple entries on the same day (Column1) - up to 6 entries per person per day. Then I condense them into 1 row on a subsequent sheet and transpose the data in Column3. The data is sorted by Date in Column1 then alphabetically in Column2.

I have some code that I don't want to post b/c I think that I've chosen a bad strategy with a Do..Until loop while indexing the Row number. I need a different strategy. I also have ~10,000 rows, so VBA is necessary and efficiency is appreciated... our lab only has 32-bit excel