Solved

VBA Code / Continue or Stop, based on value

Posted on 2013-06-12
6
262 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
6 Comments
 
LVL 29

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now