Re: [ExcelVBA] Case Without Case Select

 

Hi Tariq

You did not specify what errors you are getting but there is an unmatched With in the "Case vbNo" code block.

It might help you to understand your code better if you try to rationalize the repeated/identical code blocks. I suspect that this repetition caused the typo. If you repeat blocks of code you have to debug them individually as many times as you have repeated them.

David is quite right that your variables to hold the MsgBox results should be declared as Integer. 
However both of the following evaluate to True in VB :-
1=1
1="1"
The latter in other languages would give a type mismatch exception. 
Your If and Case comparisons actually work as you expected in spite of the mismatch.

Regards

Derek Turner

+++

>________________________________
> From: David Smart <smartware.consulting@gmail.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Friday, 6 April 2012, 13:23
>Subject: Re: [ExcelVBA] Case Without Case Select
>
>
> 
>You are putting the results of the two MsgBox calls into variables called
>Answers and Answer.
>
>However, you have defined these variables as String.
>
>MsgBox returns an integer. Your variables should be defined as integer.
>
>It seems to work OK for the IF statement, where it is presumably converting
>vbYes to a string and then comparing it to Answers.
>
>However, a Case statement is very different, and I suspect that it isn't
>doing a conversion. Hence, none of the cases match. As you don't have a
>default case to report an error, absolutely nothing seems to happen.
>
>Change your variables to integer and see if it helps.
>
>Regards, Dave S
>
>----- Original Message -----
>From: "tariq_rahmalan" <tariq_rahmalan@yahoo.com>
>To: <ExcelVBA@yahoogroups.com>
>Sent: Friday, April 06, 2012 10:59 AM
>Subject: [ExcelVBA] Case Without Case Select
>
>> Hi there. I'm new in VBA. Please give me some guidance.
>>
>> I facing this problem. Can anyone spot where the error are? Much
>> appreciated.
>>
>> Private Sub CommandButton7_Click()
>> Dim Answers As String
>> Dim Mynote As String
>> Dim Answer As String
>> Dim lRow As Long
>> Set ws = Worksheets("Rekod")
>>
>>
>> 'Place your text here
>> Mynote = "Adakah anda telah klik butang 'Tambah Rekod'?"
>>
>> 'Display Message Box
>> Answers = MsgBox(Mynote, vbQuestion + vbYesNo, "Anda Pasti?")
>>
>> If Answers = vbYes Then
>>
>> Answer = MsgBox("Adakah anda ingin save file ini?",
>> vbQuestion + vbYesNoCancel, "Save File?")
>>
>> Select Case Answer
>>
>> Case vbYes
>>
>> 'find the last row
>> lRow = ws.Cells(Rows.Count, 1) _
>> .End(xlUp).Row
>>
>> 'Copy data to the database
>> With ws
>> .Cells(lRow + 1, 1).Value = .Cells(lRow,
>> 1).Value
>> .Cells(lRow + 1, 2).Value = .Cells(lRow,
>> 2).Value
>> End With
>>
>> 'save the file
>> ThisWorkbook.Save
>>
>> 'code to proceed to next form
>> Unload Me
>> Rekod.Show
>>
>> Exit Sub
>>
>> Case vbNo 'not save the file
>>
>> 'find the last row
>> lRow = ws.Cells(Rows.Count, 1) _
>> .End(xlUp).Row
>>
>> 'Copy data to the database
>> With ws
>> .Cells(lRow + 1, 1).Value = .Cells(lRow,
>> 1).Value
>> .Cells(lRow + 1, 2).Value = .Cells(lRow,
>> 2).Value
>>
>> 'code to proceed to next form
>> Unload Me
>> Rekod.Show
>>
>> Exit Sub
>>
>> Case vbCancel
>> Unload Me
>> Rekod.Show
>> Exit Sub
>>
>> End Select
>>
>>
>> Else
>> 'code for click back butang Tambah rekod
>> Exit Sub
>> End If
>>
>>
>> End Sub
>>
>>
>>
>> ------------------------------------
>>
>> ----------------------------------
>> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
>> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
>> the ARCHIVES for EXCEL VBA.
>>
>> ----------------------------------
>> Visit our ExcelVBA group home page for more info and support files:
>> http://groups.yahoo.com/group/ExcelVBA
>>
>> ----------------------------------
>> More free tutorials and resources available at:
>> http://www.mousetrax.com
>>
>> ----------------------------------Yahoo! Groups Links
>>
>>
>>
>>
>>
>> -----
>> No virus found in this message.
>> Checked by AVG - www.avg.com
>> Version: 10.0.1424 / Virus Database: 2113/4917 - Release Date: 04/05/12
>>
>
>
>
>
>

[Non-text portions of this message have been removed]

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (3)

Recent Activity:

• New Members 6

Visit Your Group

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___