0

I have a bunch of activex text boxes and what I would like to do is change the number format for a specific number of text boxes.

Ultimately I just want one subroutine that I can code all the textboxes of my choice - textbox1, textbox2, textbox15 - the number of textbox is irrelevant and the number format will be as "###,###,###". For example...

Private sub textNumFormat_<whatever>() TextBox1.Text = Format(TextBox1.Text, "###,###,###") TextBox2.Text = Format(TextBox2.Text, "###,###,###") TextBox15.Text = Format(TextBox5.Text, "###,###,###") end sub

I'm trying to avoid not having a bunch of textbox subs. Example

Private Sub TextBox1_Change() TextBox1.Text = Format(TextBox1.Text, "###,###,###") End Sub

and then another sub...

Private Sub TextBox2_Change() TextBox2.Text = Format(TextBox2.Text, "###,###,###") End Sub

and another sub...

Private Sub TextBox15_Change() TextBox15.Text = Format(TextBox15.Text, "###,###,###") End Sub

hope it makes sense. Thanks!

I tried:

Private Sub TextBox1_Change() TextBox1 = Format(TextBox1.Value, "###,###,###") TextBox2 = Format(TextBox1.Value, "###,###,###") TextBox5 = Format(TextBox1.Value, "###,###,###") TextBox10 = Format(TextBox1.Value, "###,###,###") End Sub

but it doesn't work.

I also tried creating a general sub name and putting the format code, but didn't work either.

If someone could help me out, that would be greatly appreciated.

Thanks.

Tim
  • 35
  • 1
  • 1
  • 9

1 Answers1

0

If this is Access, you need to set textbox's .Format property. You are simply trying to set the textbox object equal to a format. Object cannot equal formats. Try something like this: TextBoxt.Format("###,###,###")

If this is a different Office apps VBA, then you will want to format the text and set the textbox's .Text property to the newly formatted text like this: TextBox1.Text = Format(TextBox1.Text, "###,###,###")

Regarding my comment below. Why not just use the workbook_open event?

Private Sub Workbook_Open()
    TextBox1 = Format(TextBox1.Value, "###,###,###")
    TextBox2 = Format(TextBox1.Value, "###,###,###")
    TextBox5 = Format(TextBox1.Value, "###,###,###")
    TextBox10 = Format(TextBox1.Value, "###,###,###")
End Sub
Tim
  • 2,701
  • 3
  • 26
  • 47
  • Thanks for your feedback. I'm coding it within Excel. Unfortunately it doesn't solve my initial question of wanting to have one sub that I could list all/any text boxes and change the format. – Tim Apr 01 '16 at 17:45
  • My bad. I thought your issue was that the code was not working. I just tested your code, and it changes the number format of all your text boxes when textbox1 is changed. Is your question how to make one sub that changes the format of multiple textboxes any time any one of them changes? For example, when I type in textbox5, they all change, and when I type in textbox2 they all change? If so, what dictates the formatting logic? IE, how do we determine which format gets used when 2 changes vs the formatting used when 5 changes? – Tim Apr 01 '16 at 17:50
  • Is [this](http://stackoverflow.com/questions/10655262/active-control-change-event-ms-access) what you're looking for? – Tim Apr 01 '16 at 18:21
  • Not really. i revised my question. hopefully it makes more sense in what i'm asking. To you earlier response, maybe "change" isn't the right object function name. Thanks. – Tim Apr 01 '16 at 18:22
  • So if the format never changes, why not just set the number format in the workbook open event? – Tim Apr 04 '16 at 16:30