PDA

View Full Version : Refresh Spreadsheet data and Pop-up message based on critera



DarinM
05-27-2015, 05:51 AM
Hi again forum!

I have a bunch of different tracking spreadsheets that I want to combine into one spreadsheet and have different tabs. Sounds like a good idea...

However, I would like it to be a tiny bit more automated/up to date WITHOUT closing the excel document.

I have a bunch of IF statements and conditional formatting, some are based on =TODAY() 's formula for dates. However I have noticed that I need to close and re-open the excel for the =TODAY() to refresh to today's true date, then the IF statements etc are up to date and correct.

So my hopeful solution for this would be two parts.

Part 1; Have a "REFRESH" button on each tab that would refresh a certain column that would have all the =TODAY() dates in them, so that they are truly TODAY's date. I would want each button on each tab to refresh all tabs (so whichever I leave open I can click on one, knowing it will refresh all).

Part 2; After clicking that "REFRESH" button, I would like a macro to search through all tabs and let me know when I need to do something, based on the existing IF & conditional formatting forumals.

For example : I have a formula that compares =TODAY() column with a "ship on this date" column, if the two match, it makes another column say "SHIP TODAY". So I would like that refresh button to go through and refresh the TODAY's dates, then if the dates match, have a Pop-up with what my one column says "SHIP TODAY".

Is this possible?

I can attach a spreadsheet if required to see more what I mean, I have some code in 1 tracker, none in another, so I'm hoping this is a one size fits all type code.

Thanks all,

jonh
05-27-2015, 07:42 AM
Sub test()
Range("a1") = Range("a1")
For Each sht In Worksheets
Set x = sht.Cells.Find(what:="ship today", LookIn:=xlValues, MatchCase:=False, SearchFormat:=False)
If Not x Is Nothing Then s = s & "Stuff to do! " & x.Parent.Name & " - " & x.Address & vbNewLine
Next
If Len(s) Then MsgBox s Else MsgBox "Put yer feet up."
End Sub

DarinM
05-27-2015, 07:43 AM
Update, just found out F9 refreshes the date, which is easy... just wondering if that refreshes the current sheet or entire workbook? If entire workbook, then I guess I just need the popup based on 1 cell/columns value.

edit; Just saw your post JonH. I will try it, if else ...I'll put my feet up! :)

thank you, i'll check back shortly

DarinM
05-27-2015, 07:48 AM
Hi JonH - That works amazingly, is there an easy way to add more than 1 "what:"ship today"'s ?

I will have about 5 or 6 things to search up and return

jonh
05-27-2015, 08:05 AM
sure, here you go..


Sub test()
MsgBox findthemall("ship today", "ship tomorrow", "ship to china")
End Sub

Private Function findthemall(ParamArray words() As Variant) As String
Range("a1").Formula = Range("a1").Formula
For Each w In words
For Each sht In Worksheets
Set x = sht.Cells.Find(what:=w, LookIn:=xlValues, MatchCase:=False, SearchFormat:=False)
If Not x Is Nothing Then findthemall = findthemall & "Found @ '" & w & _
"' in " & x.Parent.Name & " - " & x.Address & vbNewLine
Next
Next
If Len(findthemall) = 0 Then findthemall = "No matches"
End Function

DarinM
05-27-2015, 09:28 AM
Beautiful, thank you!

Do you know if F9 refreshes the entire workbook or just the active sheet? if just the active sheet, is there a button/macro I can have to update/refresh all sheets?

jonh
05-27-2015, 09:44 AM
Np.

No idea about f9. Some info here.
https://msdn.microsoft.com/en-us/library/office/bb687891.aspx

I added the formula=formula line to the code because I thought that would suffice.

DarinM
05-27-2015, 10:31 AM
Looks like F9 does entire workbook, I will do some testing to see ... however will take until tomorrow to find out :)

I appreciate your coding help! I think I have all I need, now to merge all trackers into 1.

Question though, does it matter the column that the "ship to china" is in? or just searches the entire workbook regardless of location? hoping regardless ... each tracker has a different spot for the signal words...

thanks again!

jonh
05-27-2015, 12:17 PM
It searches the entire workbook, but that can easily be changed.

DarinM
05-27-2015, 12:21 PM
Nope that works perfectly.

Thank you!!