Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA Code / Continue or Stop, based on value

Posted on 2013-06-12
6
Medium Priority
?
276 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

721 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