VBA - Skipping a Value

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
54
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
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Nine Zero

Well-known Member
Joined
Mar 10, 2016
Messages
622
instead of having them go to a Range().Value try going to Range().Text .... see if that changes anything
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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.
 

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
54

ADVERTISEMENT

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
  
...
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Strange, all textboxes have an Enabled property.:eek:

Where do you get the error?
 

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
54

ADVERTISEMENT

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"
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
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
 

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
54
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.
 

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
54
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,165
Messages
5,594,622
Members
413,918
Latest member
Mikey_C

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
Top