PDA

View Full Version : Solved: Linking the elements of a Select Case scenario



Keith_H01
01-04-2008, 03:35 AM
Greetings to you all!

I am a novice in this area, but willing to learn. My experience is pretty much limited to recording keystroke macros and subsequently amending the script.

My weakness is in declaring variables, and having suitable linkages between the different elements of code.

Here is the process that I am looking to automate:

1. A particular cell is selected
2. If the cell contains one of a number of names on a list, then data needs to be suppressed. Otherwise, no further action is needed. It is a simple Boolean Yes/No scenario.
3. The suppression procedure takes place if required.

I have been able to create a messy work-around, but I would like to know how this could be done in its entirety in VBA.

Thanking you in advance for any pointers you can give.

Regards, Keith

Keith_H01
01-04-2008, 03:37 AM
Here is the script that I was working on

Sub Macro3()
Dim GetSuppressdetails As Boolean
Dim strSecnName As String
strSecnName = ActiveCell.Value
Sheets("Balance pivot").Select
Range("A5").Select
strSecnName = ActiveCell.Value
With ActiveCell.Value
Call blnSecn(GetSuppressdetails)
If blnSecn <> 0 Then
Call Suppressor
Else: Continue
End If
End Sub
'---------------------------------------------------------
Public Function blnSecn(GetSuppressdetail) As Boolean
Select Case GetSuppressdetail
Case "Pugh"
GetSuppressdetail = 1
Case "Barney Mc Grew"
GetSuppressdetail = 1
Case "Cuthbert"
GetSuppressdetail = 1
Case "Dibble"
GetSuppressdetail = 1
Case "Grubb"
GetSuppressdetail = 1
Case Else
GetSuppressdetail = 0
End Select
blnSecn = GetSuppressdetail
End Function
'---------------------------------------------------------
Private Sub Suppressor()
MsgBox "Delete the stuff"
End Sub

rory
01-04-2008, 06:08 AM
This is a bit of a guess as I'm not entirely sure what you want:
Sub Macro3()
Dim GetSuppressdetails As Boolean
Dim strSecnName As String
Sheets("Balance pivot").Select
Range("A5").Select
strSecnName = ActiveCell.Value
GetSuppressdetails = blnSecn(strSecnName)
If GetSuppressdetails Then Call Suppressor
End Sub
Public Function blnSecn(strData) As Boolean
Select Case strData
Case "Pugh", "Barney Mc Grew", "Cuthbert", "Dibble", "Grubb"
blnSecn = True
Case Else
blnSecn = False
End Select
End Function

Oorang
01-04-2008, 06:32 AM
What you did would standardize to:
Option Explicit

Sub Example1()
Dim strSecnName As String
strSecnName = Sheets("Balance pivot").Range("A5").value
If blnSecn(cellValue) Then
Suppressor
Else
Continue
End If
End Sub
'---------------------------------------------------------
Public Function GetSuppressdetail(cellValue As String) As Boolean
Select Case cellValue
Case "Pugh"
GetSuppressdetail = True
Case "Barney Mc Grew"
GetSuppressdetail = True
Case "Cuthbert"
GetSuppressdetail = True
Case "Dibble"
GetSuppressdetail = True
Case "Grubb"
GetSuppressdetail = True
Case Else
GetSuppressdetail = False
End Select
End Function
'---------------------------------------------------------
Private Sub Suppressor()
MsgBox "Delete the stuff"
End Sub


But could probably be reduced to:

Sub Example2()
Select Case Sheets("Balance pivot").Range("A5").value
Case "Pugh", "Barney Mc Grew", "Cuthbert", "Dibble", "Grubb"
Suppressor
Case Else
Continue
End Select
End Sub

Keith_H01
01-04-2008, 08:05 AM
Rory, thanks for the tip on changing the case to True/False rather than 1/0 and for correcting the confusion that I had with the function name and the variable name in my original script. Thank you also for the de-cluttering with the IF statement.

Aaron, as you had detected, the Boolean function was dependant upon one cell, and IF the values matched those in a list, one course of action was needed, ELSE continue.

I had forgotten that for a Case, you can have several values, and there is not a limit of one value per line. The Example2 Sub Procedure is a more succinct approach than mine of gettingthe desired outcome, namely

1. Look at a given cell
2. Evaluate the content, and, based on this, to call a procedure

Many thanks for the pointers, gents. Have a pleasant weekend