Solved

VBA Code / Continue or Stop, based on value

Posted on 2013-06-12
6
261 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

743 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

12 Experts available now in Live!

Get 1:1 Help Now