Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How do I format an Excel form control from an array in a single statement ?

Posted on 2013-12-03
4
Medium Priority
?
213 Views
Last Modified: 2013-12-03
Hi,

I'm populating an Excel user form control (a textbox) from an array and then formatting the control in the next statement. Is there any way to combine these two operations into a single VBA statement. I've tried, unsuccessfully.

Me.Controls("teBasicFee" & i).Value = EnquiryItemsArray(x, 13)
Me.Controls("teBasicFee" & i).Value = Format(Me.Controls("teBasicFee" & i).Value, "#,##0.00")

Thanks
Toco
0
Comment
Question by:Tocogroup
  • 2
4 Comments
 
LVL 4

Expert Comment

by:andrew_man
ID: 39692300
with Me.Controls("teBasicFee" & i)
.Value = EnquiryItemsArray(x, 13)
.Value = Format(Me.Controls("teBasicFee" & i).Value, "#,##0.00")
End With
0
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39692301
Hi,

pls try

Me.Controls("teBasicFee" & i).Value = Format(EnquiryItemsArray(x, 13), "#,##0.00")

Open in new window

Regards
0
 

Author Comment

by:Tocogroup
ID: 39692309
Well actually I was trying to reduce the code rather than increase it. I've got a large number of controls to format and I was wanting to cut down on the coding.
0
 

Author Closing Comment

by:Tocogroup
ID: 39692358
Yes, that's it thanks. I thought i'd tried that but I must have got the syntax wrong. Thanks for your quick reply.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question