Adding a comment to Conditional Formatting via VBA

ctbanker

New Member
Joined
Aug 26, 2015
Messages
26
Hi All,

I'm very new to the VBA world and don't have much experience with coding. I want to do the following, but am not sure how to:

I have a column called "Classification", and if there is any text (example "Fee" or "Due Diligence") in a cell, I want a comment ("please fill either choose Loan Proceeds or Prepaid Deposit") to pop-up at the corresponding cell under "Paid From". I have Conditional Formatting set-up to make the cell red when the corresponding "Classification" cell is occupied, but that doesn't clearly state what I need done (and isn't as cool either).

Any help would be greatly appreciated! Please refer to a sample worksheet below...
Fees Breakdown
ClassificationPaid From:ItemDispositionAmount
FeeLoan ProceedsSBA Guarantee FeeBank Controlled Account 94,256.50
Due DiligencePrepaid DepositAppraisalGeneral Ledger Account 12,000.00
FeeLoan ProceedsUCC Search / Filing FeeBank Controlled Account 120.00
Due DiligencePrepaid DepositEnvironmentalGeneral Ledger Account 3,900.00
FeeLoan ProceedsSBA Loan Packaging FeeBank Controlled Account 2,500.00
FeePrepaid DepositFlood Determination FeeBank Controlled Account 67.50
FeeLoan ProceedsLoan Closing FeeCustomer Refund 6,569.00
FeeCOMMENT HERE 499.75
Total 119,912.75




<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
not a VBA method, nor a comment box, but a simple formula.

assuming that the first row of data is in row 3;

=IF($A3<>"","please fill either choose Loan Proceeds or Prepaid Deposit","")

if you enter that and copy it down the column, it will return your comment if column a has anything in it, or remain blank if column A is blank.

Hope that may be of some use?

But as soon as the user enters anything in where you comment is the formula is no longer in use in that cell obviusly. But i can't see it being an issue
 
Last edited:
Upvote 0
That does help. The only issue is I have a drop-down menu which gives options for "Prepaid Deposit" or "Loan Proceeds". Based off the user's choice, other formulas within the worksheet are updated. That's why I was hoping for a VBA code that would yield a pop-up box? And to take it even further, is it possible not to update anything else until the user picks one of the two listed options?
 
Upvote 0
Yes, just had a lightbulb moment, you can use data validation in the cell,

if you select data, then data validation, you will see a box pop up with three tabs,

first tab make it look like this, your list
https://www.dropbox.com/s/udgjf4cfjwyaxyj/list.jpg?dl=0


second tab, should look like this
https://www.dropbox.com/s/d3n5gxahd8sep4p/message.jpg?dl=0


and the final result will be this
https://www.dropbox.com/s/kcl9f64blnddqpk/final result.jpg?dl=0

the drop down will contain the two options to select from
 
Last edited:
Upvote 0
you can use the third tab which will add a warning, and force a correct result incse they try to type it in wrong rather than simply use the dropdown too
 
Upvote 0
That will do. Thanks!

Any idea on how to automatically send daily emails (with excel file attached) from Outlook?
 
Upvote 0
You could try a macro. And assign that macro to a button.

Sub SendEmail()
ActiveWorkbook.SendMail _
Recipients:="abc@email.com", _
Subject:="Subject header" & Format(Date, "dd/mmm/yy")
End Sub


This may work for you?
 
Upvote 0
Excel would have to be open for this to run

Code:
[FONT=arial]<code style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; background-color: rgb(238, 238, 238); white-space: inherit; "></code>[SIZE=2]<code style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 0px; padding-right: 0px; padding-bottom: 0px; padding-left: 0px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; font-size: 13px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; background-color: rgb(238, 238, 238); white-space: inherit; ">Sub AutoSend()
If Weekday(Now, vbMonday) < 6 Then 'check if weekday is < 6, starting by Monday with 1 (Sat = 6, Sun = 7)
Application.OnTime TimeValue("09:45:00"), "SendEmail"
End If
End Sub</code>[/SIZE][/FONT]
 
Upvote 0
Do I combine the previous code ie..

Sub SendEmail()
ActiveWorkbook.SendMail _
Recipients:="abc@email.com", _
Subject:="Subject header" & Format(Date, "dd/mmm/yy")
End Sub

With this code to make it work? I did that but it's not automtically sending. However, when press run, it works. Any idea why that would be?
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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
Back
Top