VBA - Skipping a Value

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
86
Office Version
  1. 2019
Platform
  1. Windows
I am trying to write a value of 0.00 to a cell, (Per Textbox2) but it keeps skipping over the value and not writing anything in the cell. I tried stopping the code and it shows up as .000 per the textbox2, but it jumps to the end of the statement. If I add a value of .001 it works, but 0 doesn't. What would cause this?

Code:
If TextBox1 = Enabled And TextBox2 = Enabled Then

    With Sheets("Sheet1")
    
    rw = .Range("B" & .Rows.Count).End(xlUp).Row + 1
    
    .Range("P" & rw).Value = TextBox1
    .Range("Q" & rw).Value = TextBox2
    .Range("E" & rw).FormulaR1C1 = "=RC[-2]-RC[11]"
    .Range("F" & rw).FormulaR1C1 = "=RC[-3]+RC[11]"
        
    End With
    
ElseIf TextBox3 = Enabled And TextBox4 = Enabled Then
    
        With Sheets("Sheet1")
    
        rw = .Range("B" & .Rows.Count).End(xlUp).Row + 1
    
        .Range("F" & rw).Value = TextBox4
        .Range("E" & rw).Value = TextBox3
    
        End With
 
ElseIf Sym = Enabled Then
    
        With Sheets("Sheet1")
    
        rw = .Range("B" & .Rows.Count).End(xlUp).Row + 1
    
        .Range("D" & rw).Value = Sym
        
         End With


End If
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
instead of having them go to a Range().Value try going to Range().Text .... see if that changes anything
 
Upvote 0
Good Catch, unfortunately still no change, still skipping.
 
Upvote 0
What are you trying to check here?
Code:
If TextBox1 = Enabled And TextBox2 = Enabled Then
The expression in that if statement is going to compare the values in the textboxes to the variable Enabled.

If you want to check if the textboxes are enabled you need to check their Enabled property.
Code:
If TextBox1.Enabled = True And TextBox2.Enabled = True Then ' = True not strictly needed.
 
Upvote 0
What I have are several textboxes that could be enabled per a drop-down box. The Bilateral Tolerance is the only one that could have a "0.000" as a value. Varying on which textbox is enabled is what and where I want the value written in my spreadsheet.

Image of my User form:
https://drive.google.com/open?id=1MrO5Hr9Dvyh8ZmR5FhFhOy1LZXpajQYx

When I try your suggestions I get a "Runtime error 438: Object doesn't support this property or method."



Textbox control: I am sure this can be written a lot better, but here is how I have the boxes being enabled true or false:

Code:
Private Sub Tol_type_Change()

If Tol_type.Value = "Bilateral" Then
    TextBox1.Enabled = True
    TextBox1.BackColor = &H80000005
    TextBox2.Enabled = True
    TextBox2.BackColor = &H80000005
    TextBox3.Enabled = False
    TextBox3.BackColor = &HC0C0C0
    TextBox4.Enabled = False
    TextBox4.BackColor = &HC0C0C0
    Sym.Enabled = False
    Sym.BackColor = &HC0C0C0
    
    ElseIf Tol_type.Value = "Limit" Then
  
...
 
Upvote 0
Strange, all textboxes have an Enabled property.:eek:

Where do you get the error?
 
Upvote 0
I am no professional coder, just enough to be dangerous and eventually get it done ;).

Should I put another Elseif and disable the textboxes that are not active?

it stops @ ".Range("P" & rw).TextBox1"
 
Upvote 0
Not sure what you are trying to do here but I'm pretty sure a range doesn't have a TextBox1 property.:)
Code:
.Range("P" & rw).TextBox1
 
Upvote 0
I am not sure how it wouldn't? The problem I have is the Bilateral tolerance is different than the other 2 options. The spreadsheet is not designed to use a bilateral tolerance. So I am writing the textbox value of .005/.000 to a cells P and Q. Then I am pasting a formula basically taking the "Nominal" value and adding\subtracting the Bilateral Tolerance in columns "E" and "F" per the next empty row. It works fine as long as each textbox has a value greater than 0.0000. The Bilateral can have a value of 0 and I need to be able to have this value show up in the spreadsheet.
 
Upvote 0
Not sure what you are trying to do here but I'm pretty sure a range doesn't have a TextBox1 property.:)
Code:
.Range("P" & rw).TextBox1

OK I re-read your reply again and you are right it is not a property. I changed the "." to "=" and that fixed it. - Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top