Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default "Continue?" Message Box

I have code that runs when a command button is triggered, that does several things, including saving the file.
After that command button is triggered, and before the existing code runs, I would like to have a message box pop up as a warning with the option to either cancel or proceed, if any of the values on the worksheet €˜Start in cells B1, B2, B3, B4, B5, B7, B8, B9, and B10 have not changed.
By that I mean, I would like that message box to launch if any of the following is true: B1=Customer, B2=Item, B3=Diameter, B4=Length, B5=Species, B7=Machine, B8=Contact, B9=Item #, and B10=INQ# OR OLD PO#
All of those default values in those cells need to be changed to different values before allowing the code to run. Is this doable?
Thank you in advance!
Paul
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default "Continue?" Message Box

Hi Paul,

Am Sun, 17 Jul 2016 13:30:31 -0700 (PDT) schrieb Paul Doucette:

I have code that runs when a command button is triggered, that does several things, including saving the file.
After that command button is triggered, and before the existing code runs, I would like to have a message box pop up as a warning with the option to either cancel or proceed, if any of the values on the worksheet ?Start? in cells B1, B2, B3, B4, B5, B7, B8, B9, and B10 have not changed.
By that I mean, I would like that message box to launch if any of the following is true: B1=?Customer?, B2=?Item?, B3=?Diameter?, B4=?Length?, B5=?Species?, B7=?Machine?, B8=?Contact?, B9=?Item #?, and B10=?INQ# OR OLD PO#?
All of those default values in those cells need to be changed to different values before allowing the code to run. Is this doable?


try following code before your existing code:

Sub Test()
Dim strVals As String
Dim varVals As Variant
Dim i As Long, j As Long

strVals = "Customer,Item,Diameter,Lenght,Species,Machine,Con tact,Item
#,INQ# OR OLD PO#"
varVals = Split(strVals, ",")

For i = LBound(varVals) To UBound(varVals)
j = i + IIf(i < 6, 1, 2)
If Cells(j, 2) = varVals(i) Then
MsgBox "Range B" & j & " is not changed", vbOKOnly
Exit Sub
End If
Next
'Your Code
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default "Continue?" Message Box

On Sunday, July 17, 2016 at 4:30:46 PM UTC-4, Paul Doucette wrote:
I have code that runs when a command button is triggered, that does several things, including saving the file.
After that command button is triggered, and before the existing code runs, I would like to have a message box pop up as a warning with the option to either cancel or proceed, if any of the values on the worksheet €˜Start in cells B1, B2, B3, B4, B5, B7, B8, B9, and B10 have not changed.
By that I mean, I would like that message box to launch if any of the following is true: B1=Customer, B2=Item, B3=Diameter, B4=Length, B5=Species, B7=Machine, B8=Contact, B9=Item #, and B10=INQ# OR OLD PO#
All of those default values in those cells need to be changed to different values before allowing the code to run. Is this doable?
Thank you in advance!
Paul


Claus-You make the complicated simple. I feel I could ask you for the formula for world peace, and you would only ask "Which world?" :-) That worked perfectly. And because my power of description was not up to including another step in my initial question, I will ask now.
On that same sheet if Cell B7=Molder, then I would like cell D8 to be checked along with the previous B1, B2, B3, B4, B5, B7, B8, B9, and B10. And if it's value has not been changed from it's default "Width", then the message box would be triggered.
Am I pushing my luck?
Thanks again for your kindness.
Paul
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default "Continue?" Message Box

Hi Paul,

Am Sun, 17 Jul 2016 14:48:58 -0700 (PDT) schrieb Paul Doucette:

On that same sheet if Cell B7=Molder, then I would like cell D8 to be checked along with the previous B1, B2, B3, B4, B5, B7, B8, B9, and B10. And if it's value has not been changed from it's default "Width", then the message box would be triggered.


I don't know if I understood your problem clearly.
Try:

Sub Test()
Dim strVals As String
Dim varVals As Variant
Dim i As Long, j As Long

strVals = "Customer,Item,Diameter,Lenght,Species,Machine,Con tact,Item
#,INQ# OR OLD PO#"
varVals = Split(strVals, ",")

For i = LBound(varVals) To UBound(varVals)
j = i + IIf(i < 6, 1, 2)
If Cells(j, 2) = varVals(i) Then
MsgBox "Range B" & j & " is not changed", vbOKOnly
Exit Sub
End If
Next
If Range("B7") & Range("D8") = "MolderWidth" Then
MsgBox "D8 is not changed yet", vbOKOnly
Exit Sub
End If
'Your Code
End Sub


Regards
Claus B.
--
Windows10
Office 2016
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default "Continue?" Message Box

On Sunday, July 17, 2016 at 4:30:46 PM UTC-4, Paul Doucette wrote:
I have code that runs when a command button is triggered, that does several things, including saving the file.
After that command button is triggered, and before the existing code runs, I would like to have a message box pop up as a warning with the option to either cancel or proceed, if any of the values on the worksheet €˜Start in cells B1, B2, B3, B4, B5, B7, B8, B9, and B10 have not changed.
By that I mean, I would like that message box to launch if any of the following is true: B1=Customer, B2=Item, B3=Diameter, B4=Length, B5=Species, B7=Machine, B8=Contact, B9=Item #, and B10=INQ# OR OLD PO#
All of those default values in those cells need to be changed to different values before allowing the code to run. Is this doable?
Thank you in advance!
Paul


You understood (or guessed) perfectly. Thankyou Claus!!!!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
"Duplicate declaration in current scope" during edit and continue Greg Lovern Excel Programming 1 April 7th 11 10:33 PM
"Continue" and Exit with For Loops Andrew Hall NZ Excel Programming 4 November 23rd 06 03:44 AM
Change default in input box from "20" to "000000"? Have macro continue on to name sheet tab? StargateFanFromWork[_4_] Excel Programming 7 October 29th 06 12:46 PM
Deleted the pivot table - continue to get the "overlap" error message [email protected] Excel Discussion (Misc queries) 0 September 27th 06 04:07 PM
Automatically click "Update Links" & "Continue" paulharvey[_13_] Excel Programming 0 June 3rd 06 05:35 PM


All times are GMT +1. The time now is 10:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"