Two VBA Questions

[WLMPilot]

1) Where can I find a color chart with numeric values to referece so I can
use BACKCOLOR?

2) I have textboxes that will be accepting numbers. How do I convert the
textbox (string) to a number for placement in cell formated as number?

Thanks,
Les

 

[Peter T]

Lots of suggestions in your previous post

Regards,
Peter T

 

[GB]

vba has eight predefined colors (although only 7 of them are really usable if
you have a white background, and only 6 give different colors if you don't
want black to be one of the "colors").

You can set your backcolor to:
vbBlack
vbBlue
vbCyan
vbGreen
vbMagenta
vbRed
vbWhite
and vbYellow.

These are members of the ColorConstants class. (Found them doing a
View->Object Browser search for Red.

As for your second question:
I thought the cells of your excel workbook could be set to match the numeric
format that you wanted and then when you pasted the value entered by the user
(I.e. if user enters 560234 pasting this number/string into the worksheet
that is setup to address money would result in $560,234.00)

If that format of copy and paste doesn't work, then you could declare a
number type that is large enough to handle the expected input values, and
then verify that the data entered is only numeric and then convert from a
string to a number and then store the number.

Otherwise you would have to check the string after each entry (text_change
or similar) remove all formatting that has been inserted, determine the new
location for the desired formatting, insert the new formatting and then
update the text box (this last item would need to be done with events
disabled to prevent reiterating the process.)

Options, and not written in excel code, but should accomplish your desired
goal.

GB

 

[FSt1]

hi
1. see this site
http://www.mvps.org/dmcritchie/excel/colors.htm
2. not sure if i understand the second question but..
Range("A1").value = textbox1.value

regards
FSt1

 

[JLGWhiz]

For color chart: VBA Help search for "PatternColorIndex Property"

To coerce strings to numbers: (re VBA Help "Conversion Functions")

myNum = CInt(myString)

Or

myNum = CLng(myString)

Or

myNum = CDbl(myString)

 

[JLGWhiz]

You can also get the Hex values of colors in VBA Help under "Color Constants"

 

[WLMPilot]

I see you reference INT, LNG, DBL in the conversion. This may be a mute
point (due to my ignorance), but the textboxes are not DIM. I assume since
they are "text"boxes that they will automatically be STRING.

Here are the the type numbers that some of the textboxes will hold:
120, 5500, 1, 39, etc. no decimal places on any numbers.
I also have a phone number that is pulled into a textbox from a cell as
(###) ###-####, then inserted into another cell on another sheet when user
has entered all data on userform. Other than the phone number, the other
numbers will be used in calcuations within the worksheet once placed in the
worksheet.

Les