Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

VBA Code / Continue or Stop, based on value

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
Geekamo
Asked:
Geekamo
1 Solution
 
Randy DownsOWNERCommented:
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
 
GeekamoAuthor Commented:
@ 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
 
FlysterCommented:
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
Independent Software Vendors: 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!

 
Randy DownsOWNERCommented:
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
 
Jerry PaladinoCommented:
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
 
GeekamoAuthor Commented:
@ 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

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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now