IF-THEN-ELSE Step 2

Hi Richard,

I appreciate VBA programming is something you are only just trying to get to grips with, I'm far from being a VBA expert though I too have received plenty of help in the past from Jon and Jan who have helped me reach a level of competence where I can now tackle many tasks on my own. I will assist you where I can however it's a bit hard to understand what your actual aim is. It would be beneficial if you explain the goal of the code and what the user is expected to experience in order to help you reach a solution quicker.

The sketch is fine. Something I have done previously when starting on a complex program I wrote was to draw out a flow-chart diagram showing the user interface and what happened whenever a user pressed a certain button or entered a certain value. It can help as a guide for how you need to lay out your code by breaking it down into individual tasks.

As for the code samples, you have provided 2 procedures called SeletRoutine (spelling mistake?) and Selections yet they do not appear to be related as one is not called from another which is what I would have initially expected i.e. it sounds like you want a dialog box to appear (called a userform), the user is then required to enter a number and then something to happen afterwards. Please be clear if that is or not the intention.

 As for the code you have provided, some observations:

• Variable declarations:
  I VBA when declaring variables (a storage location paired with a name and used to represent a particular value) you should also define the variable's type (if you don't I believe the variable gets assigned by default as a Variant). The current syntax in your code samples (i.e. Dim Var1, Var2. Var3 etc...) is used in VBscript not VBA.
  
  e.g. If you have a variable which will store text

Dim Variable_One As String

If you have a variable which will store a decimal number

Dim Variable_Two As Long

If you have a variable which will store a whole number number

Dim Variable_Three As Integer

• Multi-line text
  One of your comments mentions multi-line text, in order to do that in VBA you need to use the command vbCrLf
  If the intentional use for multi-line text was to create the userform message (as per your sketch), a better method is using the Userform objects such as individual labels.

Thank You #1 I got the code from Goggle search and attempted to modify for what I was trying to learn. the code I will stick with the one that at least works

SelectRoutine I messed up on the word Select yes. Ok working from that

I am going to post 2 pictures of the MsgBox close to what I want the achieve.

the drawing was to show what I was trying to get the Message box to do and could not.

Where it says Enter a value is where I was hoping to have the

Text example in the Sketch 

Color Green-Magenta                "button" [1]

Color Cyan-Megenta     "second button" [2]

Color Blue-Red         "then a 3rd button" [3]

 and if possible a Cancel Button       [Cancel]

looking above I do not want the text in " " just the color selections

and the Cancel button w/no explanation text

Is another example w/Cancel I was hoping to have 4 total selections

"Button1" [1]

"Button2"[2]  etc with the Cancel at the bottom. Do not really care for the long open box to put numbers in but liveable if I could get the information above the long open box for the 1, 2 or 3 plus the Cancel. Anything like that

is fine. the two macro's were to show the different Msgbox's

Lets focus on this and will work out the rest because it does work Example

i can put a 1, 2 or 3 and it will go to them. So if you know how to put the information on the screen (again this is not going to be used as it is )

trying to learn and it proves it works via the ShowStatus

I was looking at a Menu command but do not know enough to figure out how it will do a similar task. Again @ 74 June 12th I do not have the time to read a lot of books. I used Jon Summers suggestion and goggle it . It seems I cannot ask the question properly to find the answer on this part the  buttons and Text information.

Hi Richard,

I am glad your post have evolved to better format, with pictures and properly formatted code they look much better :-)

But still ... please do not mix several different questions about different issues with opinion and thoughts! It makes the question to be hardly answered. One question (issue) per post! What is your real question/issue in this discussion? Is it "problem with CHr(13)" or different versions of my code do (not) work" or something else, hidden in your text?

Why to don't ask in very simple way "Is it possible to display multi-line text in VBA MessageBox?" (as Barry Lothian mentioned already)?

Regarding to your code, I have several comments, similar to what Barry Lothian wrote:

• I do not see Option Explicit used in your code, which means ugly (and totaly wrong in my opinion) variables declaration can be used. My rule is: If there is no Option Explicit used, I do not check or comment any code, because in such case it's not about thinking about the code, but about try to find existing typing and naming errors. Tranformed to a rule this comment is: Every variable has to be defined explicitly with its Type.
• Never declare more variables on one line, it makes code worse.
• Use descriptive names.
• Declare variables just before place they are used, not all at code beginning.

Result can be:

Option Explicit
...
Private Sub <routine name>

Dim BoxTitle as String
Title = "..."

Dim BoxMessage as String
Message = "..."

Dim MessageBoxResult as String
MessageBoxResult = InputBox(...

End Sub

Other recommendations are:

• When you need to place comment, you code is probably not good enough. The code should be self-descriptive.
• When there are more options to be evaluated, use SELECT CASE, not IF THEN
• In some situations, to use : to join declaration and definition in one line can make code even more readable.
• Split code to logical / functional groups and make sub or functions from them. Subroutine code should be quite short (10 - 20 lines).
• Similar to previous recommendation, another one is "Do alwyas one thing, not more". As I often complain that you posted many different questions, thoughts and side explanations in one post, you try to do too many things in one code. Split it, focus on one task / issue / functionality only.

Result can be like this ... I think the structure and functionality is clear and well divided into own code chunk. Consequently it means every single piece of the code (sub or function) can be easily tested for its exactly defined functionality.

' Option Explicit defined somewhere on top of macro

Public Sub ShowMessageBox()
    Dim BoxTitle As String: BoxTitle = "Enter Selection to RUN"
    Dim BoxText As String: BoxText = GetBoxText
    Dim MessageBoxResult As String
    
    MessageBoxResult = InputBox(BoxText, BoxTitle, "")
    
    Select Case MessageBoxResult
        Case "1"
            ProcessBoxSelection1
        Case "2"
            ProcessBoxSelection2
        Case "3"
            ProcessBoxSelection3
        Case Else
            ProcessIncorrectBoxSelection
    End Select
End Sub

Private Function GetBoxText() As String
    GetBoxText = "Enter a value between 1 and 3:" & vbCrLf
    GetBoxText = GetBoxText & "Selection 1 - Green-Magenta" & vbCrLf
    GetBoxText = GetBoxText & "Selection 2 - Cyan-Magenta" & vbCrLf
    GetBoxText = GetBoxText & "Selection 3 - Yellow-Red"
End Function

Private Sub ProcessBoxSelection1()
    ShowStatus "Value 1 was entered"
End Sub

Private Sub ProcessBoxSelection2()
    ShowStatus "Value 2 was entered"
End Sub

Private Sub ProcessBoxSelection3()
    ShowStatus "Value 3 was entered"
End Sub

Private Sub ProcessIncorrectBoxSelection()
    ShowStatus "Incorrect value was entered"
End Sub

With regards,

  Jan

Richard Phillips said:

Again @ 74 June 12th I do not have the time to read a lot of books.

Which means you are deserved to fail every time :-(

And it's nothing that you can be proud of.

Cumulated sum of time required to solve issues and errors done because there is no strong knowledge foundation (which can be acquired reading just by one or two books at max) is always several times bigger than time required to read this book.

Regards,

  Jan