Excel VBA - Force 4 commas into a text field on userform

  • Thread starter Thread starter kazzy
  • Start date Start date
K

kazzy

Hi Excel VBA advisers!

I have a userform where there are two text boxes (drugdesc1 &
drugdesc2) that I want to force 4 commas to be keyed into both fields.
4 commas are MANDATORY in each text box & are used to separate the
type of input within each. ie; brand name, ingredients, strength,
form, pack size. (FYI: Later on, I split these fields into 4 separate
columns based on the comma delimiter.)

When "cmdAdd" command button is clicked, I need to display a msgbox to
advise user and disallow the add.

I imagine that the code (repeated for each text box) that I would put
in the "sub cmdAdd" procedure would go something like this:

If Me.txtdrugdesc1.Value = ' this is the part I need help with.
Perhaps it's the =Len function?

Then
MsgBox "Please key in four commas into Drug Description 1!",
vbExclamation, "Commas are missing in drug description!"
txtdrugdesc1.SetFocus
Exit sub
End If

Per above comment, I don't know how to count the number of commas in
the text box but I suspect the =Len function could be used but I
cannot figure how to use it.

I would be grateful for advice please!
 
kazzy explained :
Hi Excel VBA advisers!

I have a userform where there are two text boxes (drugdesc1 &
drugdesc2) that I want to force 4 commas to be keyed into both fields.
4 commas are MANDATORY in each text box & are used to separate the
type of input within each. ie; brand name, ingredients, strength,
form, pack size. (FYI: Later on, I split these fields into 4 separate
columns based on the comma delimiter.)

When "cmdAdd" command button is clicked, I need to display a msgbox to
advise user and disallow the add.

I imagine that the code (repeated for each text box) that I would put
in the "sub cmdAdd" procedure would go something like this:

If Me.txtdrugdesc1.Value = ' this is the part I need help with.
Perhaps it's the =Len function?

Then
MsgBox "Please key in four commas into Drug Description 1!",
vbExclamation, "Commas are missing in drug description!"
txtdrugdesc1.SetFocus
Exit sub
End If

Per above comment, I don't know how to count the number of commas in
the text box but I suspect the =Len function could be used but I
cannot figure how to use it.

I would be grateful for advice please!
Click to expand...

I advise that you revise your approach.

Firstly, splitting your delimited string into individual elements
result in 5 columns, not 4.

This would be way lots easier to manage using separate textboxes for
each piec of data. You could then insert the commas yourself OR just
transfer each value directly to its target cell if you have no other
use for a delimited string.

I realize this means 10 textboxes but you could group them inside a
frame and use a loop to process them.

So...
Frame1 Frame2
txtDrugDesc1a txtDrugDesc2a
txtDrugDesc1b txtDrugDesc2b
txtDrugDesc1c txtDrugDesc2c
txtDrugDesc1d txtDrugDesc2d
txtDrugDesc1e txtDrugDesc2e

Using the index of a loop makes placing each value into the appropriate
row/column very easy.
 
kazzy explained :













I advise that you revise your approach.

Firstly, splitting your delimited string into individual elements
result in 5 columns, not 4.

This would be way lots easier to manage using separate textboxes for
each piec of data. You could then insert the commas yourself OR just
transfer each value directly to its target cell if you have no other
use for a delimited string.

I realize this means 10 textboxes but you could group them inside a
frame and use a loop to process them.

So...
  Frame1                    Frame2
    txtDrugDesc1a             txtDrugDesc2a
    txtDrugDesc1b             txtDrugDesc2b
    txtDrugDesc1c             txtDrugDesc2c
    txtDrugDesc1d             txtDrugDesc2d
    txtDrugDesc1e             txtDrugDesc2e

Using the index of a loop makes placing each value into the appropriate
row/column very easy.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks for your speedy response Garry. I would so like to be able to
do that.

However, I should have also explained that the user will be copying
the string of text from another source (a medical software pckge) into
those text boxes, so they don't have to retype the text. Therefore
quicker & less errors if commas are placed into the pasted text. I
wish I could grab the text myself from the other s/w pkge but even if
that were possible, it would require a more expertise than I have.
 
Thanks for your speedy response Garry. I would so like to be able to
do that.

However, I should have also explained that the user will be copying
the string of text from another source (a medical software pckge) into
those text boxes, so they don't have to retype the text. Therefore
quicker & less errors if commas are placed into the pasted text. I
wish I could grab the text myself from the other s/w pkge but even if
that were possible, it would require a more expertise than I have.- Hide quoted text -

- Show quoted text -
Click to expand...

Oh & you are right, there will be 5 columns, not 4. :-)
 
kazzy submitted this idea :
Thanks for your speedy response Garry. I would so like to be able to
do that.

However, I should have also explained that the user will be copying
the string of text from another source (a medical software pckge) into
those text boxes, so they don't have to retype the text. Therefore
quicker & less errors if commas are placed into the pasted text. I
wish I could grab the text myself from the other s/w pkge but even if
that were possible, it would require a more expertise than I have.
Click to expand...

The problem yu face is whether the pasted text is consistent in
structure. For example, each piece of info will need to be exactly the
same OR be naturally separated by a space. The nature of your example
suggests this data will/can vary greatly. Is there any reason why you
can't put it into 1 cell and parse out the values into other cells via
spreadsheet formulas. Whether formula or VBA, you'll still need to know
the way each piece of data is positioned in the text.

Perhaps you can post several examples so we can get an idea of it.

Optionally, does the medical software pkg have the ability to export to
Excel already built in? What's the name of this software?
 
kazzy submitted this idea :








The problem yu face is whether the pasted text is consistent in
structure. For example, each piece of info will need to be exactly the
same OR be naturally separated by a space. The nature of your example
suggests this data will/can vary greatly. Is there any reason why you
can't put it into 1 cell and parse out the values into other cells via
spreadsheet formulas. Whether formula or VBA, you'll still need to know
the way each piece of data is positioned in the text.

Perhaps you can post several examples so we can get an idea of it.

Optionally, does the medical software pkg have the ability to export to
Excel already built in? What's the name of this software?

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -
Click to expand...

Yes I realise that the data must be consistent in structure & it will
be. I don't have an example to post here but it will be text like:

Aspro Clear saccharin sodium 150mg tablets 24

Where:
Aspro Clear = Brand name
saccharin sodium = Ingredients
150mg = Strength
tablets = Form
24 = Pack Size

After pasting the text string into the userform form, the user will
place commas in, where appropriate. The medical s/w cannot easily
export this data. Alternative processess have already been discussed
with the user & this is their decision to go this way. Their reason is
that it's not a big volume & want to keep it simple which is cutting &
pasting to the spreadsheet (via userform).

Garry, do you have a suggestion how I can validate that the user has
placed 4 commas in a text field please?
 
Garry, do you have a suggestion how I can validate that the
user has placed 4 commas in a text field please?
Click to expand...

Perhaps the simplest way would be this...

If UBound(Split(TextBox1.Value, ",")) = 4 Then
' There are 4 commas in the text.
Else
' There are not 4 commas in the text.
End If

Rick Rothstein (MVP - Excel)
 
Perhaps the simplest way would be this...

If UBound(Split(TextBox1.Value, ",")) = 4 Then
    '  There are 4 commas in the text.
Else
    '  There are not 4 commas in the text.
End If

Rick Rothstein (MVP - Excel)
Click to expand...

Thank you to both for your time & effort to respond.

Rick, that idea worked beautifully. Thank you.

I changed it slightly to:

If UBound(Split(txtdrugdesc1.Value, ",")) <> 4 Then
MsgBox "Please ensure there are 4 commas in 'Drug Name -
existing drugs only' field.", vbExclamation, "Four commas are
mandatory"
txtdrugdesc1.SetFocus
Exit Sub
End If

I don't have anyone here to help me & after a great many weeks on self
teaching & ongoing stumbling blocks, I do get very frustrated with
this VBA. I'll probably be back!
 
Back
Top