Format Textbox not working

[tbeards]

I am having trouble with some code that is puzzling me. Basically, I have a userform that upon opening, I want the textboxes to show values from a spreadsheet in the same format as the spreadsheet. The spreadsheet uses named ranges ("Annual_Gallons", "GallonsLowTier1_txt" and "GallonsLowTier2_txt"). The formatting works for the Annual_Gallons but doesn't for the other two. For example, in my spreadsheet I have 50,000 for GallonsLowTier1_txt and it is appearing without the , (comma). The first one, "Annual Gallons", is showing up just fine with the comma (value is 258,568). I am puzzled. Any ideas? My text box names are the same as the cell named ranges and I have double checked that they are spelled the same...

Thanks beforehand!

Private Sub UserForm_Initialize()

FuelRebateVolumeGallons_txt.Value = Format(Worksheets(constSheetFuel).Range("Annual_Gallons").Value, "#,##0") 'works
GallonsLowTier1_txt.Value = Format(Worksheets(constSheetAdministration).Range("GallonsLowTier1_txt").Value, "#,##0") 'doesn't work
GallonsLowTier2_txt.Value = Format(Worksheets(constSheetAdministration).Range("GallonsLowTier2_txt").Value, "#,##0") 'doesn't work


End Sub

 

[Norie]

Use Text instead of Value.

That should return the value from the worksheet as it is formatted on the worksheet.

 

[tbeards]

Boy oh boy do I feel dumb. I realized I had the Control Source property populated with the values I was bringing into the userform, except the one that worked!. I deleted all of the CS properties and it works.

Thanks for the reply though as that worked as well!

~t

 

[tbeards]

I mean your post worked as well as the .value but it wouldn't have worked the way it was before I deleted the CS properties.

Thanks again!