Solved

VBA Code / Continue or Stop, based on value

Posted on 2013-06-12
6
273 Views
Last Modified: 2013-06-15
Hello Experts,

I have a few buttons in my workbook, that when pressed - execute some code.  My problem is that I don't want code to run - UNLESS all required fields are filled in.

I created a formula that returns a TRUE/FALSE - basically saying if the code should run or not.

Named Cell: "AllowButtonActions" - will contain either TRUE or FALSE

Here is the code

Sub Pallet_Tag_PrintSelector()
    Dim printSelection As Integer
    
    printSelection = Range("PrintSelection").Value
    If printSelection = 0 Then
        Call Pallet_Tag_PrintAll
    Else
        MsgBox printSelection
        'ThisWorkbook.Worksheets("Preview").PrintOut
    End If
        Range("PrintSelection").Value = ""
End Sub
Sub Pallet_Tag_PrintAll()
    Dim i As Integer
    Dim palletCount As Integer
    
    palletCount = Range("TotalPallets").Value
    
    For i = 1 To palletCount
        Range("PrintSelection").Value = i
        MsgBox printSelection
        'ThisWorkbook.Worksheets("Preview").PrintOut
    Next 'i

        Range("PrintSelection").Value = ""

End Sub

Open in new window


What I would like is this...

If "AllowButtonActions" has a value of TRUE - then the code above runs like normal.

If "AllowButtonActions" has a value of FALSE - then the code needs to do two things.

Step 1:  Show message box, (Title: "Title goes here" & Body: "Body message goes here!") with ONLY an "OK" button showing.
Step 2:  Code needs to stop.

I tried to do this myself, but everything I did - ended up in error after error. I just barely get by when it comes to VBA.

Thank you in advance for your help!

~ Geekamo
0
Comment
Question by:Geekamo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 30

Expert Comment

by:Randy Downs
ID: 39243471
Try this. You want your code to run in the IF loop. You can discard the color changes if you like.

http://www.ehow.com/how_8096568_activate-toggle-button-vba.html

"ToggleButton1Click event:

Private Sub ToggleButton1_Click()

If ToggleButton1.Value = True Then

' Set UserForm background to Red.

Me.BackColor = RGB(255, 0, 0)

Else

' Set UserForm background to Blue.

Me.BackColor = RGB(0, 0, 255)

End If

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Geekamo
ID: 39243501
@ Number-1,

Thank you for the response. Unfortunately, I'm still lost. I barely grasp VBA, while I can somewhat read through code and understand what is happening - I rarely have luck writing the VBA myself and it actually working.

~ Geekamo
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39243507
Enter this if statement at the beginning of your code.:

    If AllowButtonActions.Value = False Then
        msg = MsgBox("Body Message Here", vbOKOnly, "Enter Title Here")
        Exit Sub
    End If

I'm not quite sure what you mean by
Named Cell: "AllowButtonActions"
. If it's a cell on a sheet, use "If Range("A1").Value = False then". Dim msg as String.

Flyster
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:Randy Downs
ID: 39243517
The sample I quoted is changing the background of the button based on the value of ToggleButton1. In your case it would be the value of AllowButtonActions.

If you look at the link I provided it guides you through the entire process.
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 500 total points
ID: 39244360
The code below retrieves the TRUE or FALSE value from the Defined Name "AllowButtonActions" and then tests that value in the IF Statement.  If it is FALSE the code stops.  If it is TRUE the code continues.
Option Explicit

Sub Pallet_Tag_PrintSelector()
    Dim PrintSelection As Integer
    Dim AllowButtonActions As Boolean
    Dim DisplayMsg As String

    ' Get TRUE or FALSE value from Defined Name "AllowButtonActions"
    AllowButtonActions = Range("AllowButtonActions").Value
    
    ' Test the AllowButtonActions value to determine if the Proc should continue
    If Not AllowButtonActions Then
        DisplayMsg = MsgBox("Enter Msg Body Here", vbOKOnly, "Enter Title Here")
        Exit Sub
    End If

    PrintSelection = Range("PrintSelection").Value
    If PrintSelection = 0 Then
        Call Pallet_Tag_PrintAll
    Else
        MsgBox PrintSelection
        'ThisWorkbook.Worksheets("Preview").PrintOut
    End If
        Range("PrintSelection").Value = ""
End Sub
Sub Pallet_Tag_PrintAll()
    Dim i As Integer
    Dim palletCount As Integer
    Dim PrintSelection As Integer
        
    palletCount = Range("TotalPallets").Value
    
    For i = 1 To palletCount
        Range("PrintSelection").Value = i
        MsgBox PrintSelection
        'ThisWorkbook.Worksheets("Preview").PrintOut
    Next 'i

        Range("PrintSelection").Value = ""

End Sub

Open in new window

Thanks,
Jerry
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39250838
@ All,

Thank you all for your input.

@ Jerry,

Out of all the solutions provided, yours worked flawlessly!  Thank you very much. I appreciate your help!

~ Geekamo
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question