Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

error in calculation when use numbrformat in textbox on form

0

Hello,

I try when match name in combobox1 with column B and if I select optionbutton1 or optionbutton2 and fill amount like "#,##0.00" in textbox1 then will sum over value in column C for the name based on optionbutton1,will sum over value in column D based on optionbutton2. 

my problem  doesn't calculate correctly  when use number format and show mssage after calculation, should not show the message  except in  one case if I click commandbutton1 an no selected optionbuttons!

Private Sub CommandButton1_Click()
  Dim f As Range

  With ComboBox1
    If .Value = "" Then
      MsgBox "Enter Name", vbCritical
      .SetFocus
      Exit Sub
    End If
    Set f = Range("B:B").Find(.Value, , xlValues, xlWhole, , , False)
    If f Is Nothing Then
      MsgBox "Name does not exists", vbExclamation
      .SetFocus
      Exit Sub
    End If
  End With
  With TextBox1
    If .Value = "" Or Not IsNumeric(.Value) Then
      MsgBox "Enter Amount", vbInformation
      .SetFocus
      Exit Sub
    End If
  End With

  With f.Offset(, 1)
  If OptionButton1.Value = True Then
  .Value = .Value + Val(TextBox1.Value)
  Else
   MsgBox "No option was selected", vbCritical
  End If
 End With
  With f.Offset(, 2)
  If OptionButton2.Value = True Then

  .Value = .Value + Val(TextBox1.Value)
  Else
   MsgBox "No option was selected", vbCritical
  End If
   End With
  TextBox1.Value = ""
  ComboBox1.Value = ""
  Label4.Caption = ""
  OptionButton1.Value = False
  OptionButton2.Value = False
  End Sub

I look forward any body to help me?

Answer
Discuss

Answers

0
Selected Answer

Alla

No time to explain but Val("1,200.00") returns 1 so try converting to the value to a Double instead, e.g.

  With f.Offset(, 1)
  If OptionButton1.Value = True Then
  .Value = .Value + CDbl(TextBox1.Value)

Revision #1: 23 May 2024

The following code is in the attached revised file and should give the error message only when no option buttonwas selected (changes in bold):

Private Sub CommandButton1_Click()
  Dim f As Range


  With ComboBox1
    If .Value = "" Then
      MsgBox "Enter Name", vbCritical
      .SetFocus
      Exit Sub
    End If
    Set f = Range("B:B").Find(.Value, , xlValues, xlWhole, , , False)
    If f Is Nothing Then
      MsgBox "Name does not exists", vbExclamation
      .SetFocus
      Exit Sub
    End If
  End With
  With TextBox1
    If .Value = "" Or Not IsNumeric(.Value) Then
      MsgBox "Enter Amount", vbInformation
      .SetFocus
      Exit Sub
    End If
  End With

  With f.Offset(, 1)
  If OptionButton1.Value = True Then
  .Value = .Value + CDbl(TextBox1.Value)

  ElseIf OptionButton2.Value = False Then

   MsgBox "No option was selected", vbCritical
   Exit Sub
  End If
 End With
  With f.Offset(, 2)
  If OptionButton2.Value = True Then

  .Value = .Value + CDbl(TextBox1.Value)

  End If
   End With
  TextBox1.Value = ""
  ComboBox1.Value = ""
  Label4.Caption = ""
  OptionButton1.Value = False
  OptionButton2.Value = False


  End Sub

Hope this fixes your problems- if so, please mark this Answer as Selected.

Discuss

Discussion

Hi,,
I still face problem show messagebox after calculation !
Alaa (rep: 30) May 23, '24 at 12:46 pm
Sorry Alaa but I had little time to answer you and just (hopefully) the calculation problem.

I may have some time later to check. Does the example in your question still give the message? (Can't check on my mobile phone ) 
John_Ru (rep: 6212) May 23, '24 at 1:36 pm
to clarification :
the message shows after click commandbutton1 and after message gone then will calculation.
but the right way if I click commandbutton1 without  select optiobutton then should show the message  and doesn't calculate before select optionbutton..
and if I select optionbutton and fill combobox1,textbox1 , then should calculate without showing any message.
Alaa (rep: 30) May 23, '24 at 3:00 pm
Thanks Alaa. Please see Revision #1: 23 May 2024 to my Answer, plus revised file.
John_Ru (rep: 6212) May 23, '24 at 5:18 pm
It's solved my problem .
many thanks, John.
Alaa (rep: 30) May 23, '24 at 5:39 pm
Great! Thanks for selecting my Answer, Alaa. 
John_Ru (rep: 6212) May 23, '24 at 5:45 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login