PC Review


Reply
Thread Tools Rate Thread

Excel VBA - Userform Textbox problem

 
 
thesteelmaker
Guest
Posts: n/a
 
      20th Mar 2004
The code at the bottom gives me a textbox, formatted to 1 dp. Som
vailidation to stop "text" enteries and some validation to give
positive number.

All is ok so far.

On doing some testing, the text box lets me do some strange things
that i do not want, i.e.:

Enter into text box

6.6.6 this changes to 0.3
6 7 changes to 38174.0
5/8/0 changes to 36743.0

I take it the last two are accepted as dates.

why?

These are errors that i dont really want.

Any suggestions would be helpful.

Thanks

Code=================================
Private Sub TextBox1_BeforeUpdate(ByVal Cancel A
MSForms.ReturnBoolean)

' Set number format
TextBox1.Text = Format$(frmTest.TextBox1.Text, "####0.0")

' Check the value entered is numeric
If Not IsNumeric(TextBox1.Value) Then
MsgBox "You entered a non-numeric value, try again."
vbExclamation, "Error"
TextBox1.SetFocus
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
Cancel = True
Else

' Check the value is positive
If (TextBox1.Text < 0) Then
MsgBox "You inserted a negative number, try again."
vbExclamation, "Error"
TextBox1.SetFocus
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
Cancel = True
End If
End If
End Sub

Private Sub UserForm_Initialize()
TextBox1.Text = ""
TextBox2.Text = ""
TextBox1.SetFocus
End Su

--
Message posted from http://www.ExcelForum.com

 
Reply With Quote
 
 
 
 
Harald Staff
Guest
Posts: n/a
 
      20th Mar 2004
Hi

You don't need all that. If positive decimal numbers are the only entries
allowed then use simply

Private Sub TextBox1_KeyDown(ByVal KeyCode As _
MSForms.ReturnInteger, ByVal Shift As Integer)
If Shift = 2 Then
If KeyCode = 86 Then KeyCode = 0
End If
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii _
As MSForms.ReturnInteger)
Select Case KeyAscii
Case 46
If InStr(TextBox1.Text, ".") > 0 Then _
KeyAscii = 0
Case 48 To 57
Case Else
KeyAscii = 0
End Select
End Sub

Now it's impossible to type anything unwanted in there and you don't have to
annoy your users with modal textboxes popping up telling them how stupid
they are. They hate that, and after a while they hate the application
too -trust me on this.

HTH. Best wishes Harald

"thesteelmaker >" <<(E-Mail Removed)> skrev i
melding news:(E-Mail Removed)...
> The code at the bottom gives me a textbox, formatted to 1 dp. Some
> vailidation to stop "text" enteries and some validation to give a
> positive number.
>
> All is ok so far.
>
> On doing some testing, the text box lets me do some strange things,
> that i do not want, i.e.:
>
> Enter into text box
>
> 6.6.6 this changes to 0.3
> 6 7 changes to 38174.0
> 5/8/0 changes to 36743.0
>
> I take it the last two are accepted as dates.
>
> why?
>
> These are errors that i dont really want.
>
> Any suggestions would be helpful.
>
> Thanks
>
> Code=================================
> Private Sub TextBox1_BeforeUpdate(ByVal Cancel As
> MSForms.ReturnBoolean)
>
> ' Set number format
> TextBox1.Text = Format$(frmTest.TextBox1.Text, "####0.0")
>
> ' Check the value entered is numeric
> If Not IsNumeric(TextBox1.Value) Then
> MsgBox "You entered a non-numeric value, try again.",
> vbExclamation, "Error"
> TextBox1.SetFocus
> TextBox1.SelStart = 0
> TextBox1.SelLength = Len(TextBox1.Text)
> Cancel = True
> Else
>
> ' Check the value is positive
> If (TextBox1.Text < 0) Then
> MsgBox "You inserted a negative number, try again.",
> vbExclamation, "Error"
> TextBox1.SetFocus
> TextBox1.SelStart = 0
> TextBox1.SelLength = Len(TextBox1.Text)
> Cancel = True
> End If
> End If
> End Sub
>
> Private Sub UserForm_Initialize()
> TextBox1.Text = ""
> TextBox2.Text = ""
> TextBox1.SetFocus
> End Sub
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>



 
Reply With Quote
 
 
 
 
Dave Peterson
Guest
Posts: n/a
 
      20th Mar 2004
Just to add to Harald's suggestion:

This portion:

If Shift = 2 Then
If KeyCode = 86 Then KeyCode = 0
End If

Stops ctrl-V (pasting into the textbox).


Harald Staff wrote:
>
> Hi
>
> You don't need all that. If positive decimal numbers are the only entries
> allowed then use simply
>
> Private Sub TextBox1_KeyDown(ByVal KeyCode As _
> MSForms.ReturnInteger, ByVal Shift As Integer)
> If Shift = 2 Then
> If KeyCode = 86 Then KeyCode = 0
> End If
> End Sub
>
> Private Sub TextBox1_KeyPress(ByVal KeyAscii _
> As MSForms.ReturnInteger)
> Select Case KeyAscii
> Case 46
> If InStr(TextBox1.Text, ".") > 0 Then _
> KeyAscii = 0
> Case 48 To 57
> Case Else
> KeyAscii = 0
> End Select
> End Sub
>
> Now it's impossible to type anything unwanted in there and you don't have to
> annoy your users with modal textboxes popping up telling them how stupid
> they are. They hate that, and after a while they hate the application
> too -trust me on this.
>
> HTH. Best wishes Harald
>
> "thesteelmaker >" <<(E-Mail Removed)> skrev i
> melding news:(E-Mail Removed)...
> > The code at the bottom gives me a textbox, formatted to 1 dp. Some
> > vailidation to stop "text" enteries and some validation to give a
> > positive number.
> >
> > All is ok so far.
> >
> > On doing some testing, the text box lets me do some strange things,
> > that i do not want, i.e.:
> >
> > Enter into text box
> >
> > 6.6.6 this changes to 0.3
> > 6 7 changes to 38174.0
> > 5/8/0 changes to 36743.0
> >
> > I take it the last two are accepted as dates.
> >
> > why?
> >
> > These are errors that i dont really want.
> >
> > Any suggestions would be helpful.
> >
> > Thanks
> >
> > Code=================================
> > Private Sub TextBox1_BeforeUpdate(ByVal Cancel As
> > MSForms.ReturnBoolean)
> >
> > ' Set number format
> > TextBox1.Text = Format$(frmTest.TextBox1.Text, "####0.0")
> >
> > ' Check the value entered is numeric
> > If Not IsNumeric(TextBox1.Value) Then
> > MsgBox "You entered a non-numeric value, try again.",
> > vbExclamation, "Error"
> > TextBox1.SetFocus
> > TextBox1.SelStart = 0
> > TextBox1.SelLength = Len(TextBox1.Text)
> > Cancel = True
> > Else
> >
> > ' Check the value is positive
> > If (TextBox1.Text < 0) Then
> > MsgBox "You inserted a negative number, try again.",
> > vbExclamation, "Error"
> > TextBox1.SetFocus
> > TextBox1.SelStart = 0
> > TextBox1.SelLength = Len(TextBox1.Text)
> > Cancel = True
> > End If
> > End If
> > End Sub
> >
> > Private Sub UserForm_Initialize()
> > TextBox1.Text = ""
> > TextBox2.Text = ""
> > TextBox1.SetFocus
> > End Sub
> >
> >
> > ---
> > Message posted from http://www.ExcelForum.com/
> >


--

Dave Peterson
(E-Mail Removed)
 
Reply With Quote
 
thesteelmaker
Guest
Posts: n/a
 
      20th Mar 2004
Thank you very much.

I'm very impressed.

I've not come across any reference to keycodes, or anything in yo
coding.

Could you explain a bit more.

Thanks.

Private Sub TextBox1_KeyDown(ByVal KeyCode As _
MSForms.ReturnInteger, ByVal Shift As Integer)
If Shift = 2 Then
If KeyCode = 86 Then KeyCode = 0
End If
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii _
As MSForms.ReturnInteger)
Select Case KeyAscii
Case 46
If InStr(TextBox1.Text, ".") > 0 Then _
KeyAscii = 0
Case 48 To 57
Case Else
KeyAscii = 0
End Select
End Su

--
Message posted from http://www.ExcelForum.com

 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      20th Mar 2004
Try adding this to a test form:

Option Explicit
Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
MsgBox KeyCode
End Sub

And you'll see what each keystroke represents.
(hit ctrl and don't let go. Then hit V and you'll see the 86.)

You can search VBA's help for "keycode constants" and see vbKeyV and how it
represents 86.



"thesteelmaker <" wrote:
>
> Thank you very much.
>
> I'm very impressed.
>
> I've not come across any reference to keycodes, or anything in you
> coding.
>
> Could you explain a bit more.
>
> Thanks.
>
> Private Sub TextBox1_KeyDown(ByVal KeyCode As _
> MSForms.ReturnInteger, ByVal Shift As Integer)
> If Shift = 2 Then
> If KeyCode = 86 Then KeyCode = 0
> End If
> End Sub
>
> Private Sub TextBox1_KeyPress(ByVal KeyAscii _
> As MSForms.ReturnInteger)
> Select Case KeyAscii
> Case 46
> If InStr(TextBox1.Text, ".") > 0 Then _
> KeyAscii = 0
> Case 48 To 57
> Case Else
> KeyAscii = 0
> End Select
> End Sub
>
> ---
> Message posted from http://www.ExcelForum.com/


--

Dave Peterson
(E-Mail Removed)
 
Reply With Quote
 
Jon Peltier
Guest
Posts: n/a
 
      21st Mar 2004
Well, isn't this nice. This morning I was wondering how I was going to
tell whether the user typed or pasted his information into the textbox!
Thanks Dave and Harald.

(An additional coincidence is the OP's handle, given that my real
technical training is in metallurgy.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Dave Peterson wrote:

> Try adding this to a test form:
>
> Option Explicit
> Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
> ByVal Shift As Integer)
> MsgBox KeyCode
> End Sub
>
> And you'll see what each keystroke represents.
> (hit ctrl and don't let go. Then hit V and you'll see the 86.)
>
> You can search VBA's help for "keycode constants" and see vbKeyV and how it
> represents 86.
>
>
>
> "thesteelmaker <" wrote:
>
>>Thank you very much.
>>
>>I'm very impressed.
>>
>>I've not come across any reference to keycodes, or anything in you
>>coding.
>>
>>Could you explain a bit more.
>>
>>Thanks.
>>
>>Private Sub TextBox1_KeyDown(ByVal KeyCode As _
>>MSForms.ReturnInteger, ByVal Shift As Integer)
>>If Shift = 2 Then
>>If KeyCode = 86 Then KeyCode = 0
>>End If
>>End Sub
>>
>>Private Sub TextBox1_KeyPress(ByVal KeyAscii _
>>As MSForms.ReturnInteger)
>>Select Case KeyAscii
>>Case 46
>>If InStr(TextBox1.Text, ".") > 0 Then _
>>KeyAscii = 0
>>Case 48 To 57
>>Case Else
>>KeyAscii = 0
>>End Select
>>End Sub
>>
>>---
>>Message posted from http://www.ExcelForum.com/

>
>


 
Reply With Quote
 
Harald Staff
Guest
Posts: n/a
 
      21st Mar 2004
Hi Guys

Just adding a few minor things.

Jon: The paste code is pretty raw as you noticed. My real life code for
those events is
- cancel
- read the clipboard content
- if acceptable then paste it in, or paste the "acceptable part" in
but it's pretty large and confusing, so I added the cancel part only for
demonstration.

Steelmaker: Many built-in events provide variables that it's code can
validate and change.For example in ThisWorkbook:

Private Sub Workbook_BeforeSave(ByVal _
SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub

Now this workbook will never save because we cancel it. Not useful as is,
but add a condition or two to see if the save location is ok, if this and
that is completely filled out, ...

TextBox1_KeyDown uses Keycode, the number of the keyboard key pressed. "a"
and "A" is the same key, number 65, the diference is Shift, which is 0 for a
and 1 for A. Ctrl is 2. A very useful keycode to trap is 13, the Enter key,
usually meaning that the entry is finished so go to next field or click the
OK button or something.

TextBox1_KeyPress uses KeyAscii, the resulting ascii value for the entry,
where A is 65 and a is 97. For testing and development I've found that
putting the value in question up in the form caption and/or in the immediate
window is helpful:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Me.Caption = KeyAscii
Debug.Print KeyAscii
End Sub

HTH. Best wishes Harald

"Jon Peltier" <(E-Mail Removed)> skrev i melding
news:(E-Mail Removed)...
> Well, isn't this nice. This morning I was wondering how I was going to
> tell whether the user typed or pasted his information into the textbox!
> Thanks Dave and Harald.
>
> (An additional coincidence is the OP's handle, given that my real
> technical training is in metallurgy.)
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> http://PeltierTech.com/Excel/Charts/
> _______
>
> Dave Peterson wrote:
>
> > Try adding this to a test form:
> >
> > Option Explicit
> > Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
> > ByVal Shift As Integer)
> > MsgBox KeyCode
> > End Sub
> >
> > And you'll see what each keystroke represents.
> > (hit ctrl and don't let go. Then hit V and you'll see the 86.)
> >
> > You can search VBA's help for "keycode constants" and see vbKeyV and how

it
> > represents 86.
> >
> >
> >
> > "thesteelmaker <" wrote:
> >
> >>Thank you very much.
> >>
> >>I'm very impressed.
> >>
> >>I've not come across any reference to keycodes, or anything in you
> >>coding.
> >>
> >>Could you explain a bit more.
> >>
> >>Thanks.
> >>
> >>Private Sub TextBox1_KeyDown(ByVal KeyCode As _
> >>MSForms.ReturnInteger, ByVal Shift As Integer)
> >>If Shift = 2 Then
> >>If KeyCode = 86 Then KeyCode = 0
> >>End If
> >>End Sub
> >>
> >>Private Sub TextBox1_KeyPress(ByVal KeyAscii _
> >>As MSForms.ReturnInteger)
> >>Select Case KeyAscii
> >>Case 46
> >>If InStr(TextBox1.Text, ".") > 0 Then _
> >>KeyAscii = 0
> >>Case 48 To 57
> >>Case Else
> >>KeyAscii = 0
> >>End Select
> >>End Sub
> >>
> >>---
> >>Message posted from http://www.ExcelForum.com/

> >
> >

>



 
Reply With Quote
 
Jon Peltier
Guest
Posts: n/a
 
      23rd Mar 2004
Harald old pal -

How do you read the clipboard content? I was letting the paste occur
into a textbox, then I parsed that to turn garbage into data. But
reading the clipboard content sounds much more elegant.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
http://PeltierTech.com/Excel/Charts/
_______

Harald Staff wrote:

> Hi Guys
>
> Just adding a few minor things.
>
> Jon: The paste code is pretty raw as you noticed. My real life code for
> those events is
> - cancel
> - read the clipboard content
> - if acceptable then paste it in, or paste the "acceptable part" in
> but it's pretty large and confusing, so I added the cancel part only for
> demonstration.
>
> Steelmaker: Many built-in events provide variables that it's code can
> validate and change.For example in ThisWorkbook:
>
> Private Sub Workbook_BeforeSave(ByVal _
> SaveAsUI As Boolean, Cancel As Boolean)
> Cancel = True
> End Sub
>
> Now this workbook will never save because we cancel it. Not useful as is,
> but add a condition or two to see if the save location is ok, if this and
> that is completely filled out, ...
>
> TextBox1_KeyDown uses Keycode, the number of the keyboard key pressed. "a"
> and "A" is the same key, number 65, the diference is Shift, which is 0 for a
> and 1 for A. Ctrl is 2. A very useful keycode to trap is 13, the Enter key,
> usually meaning that the entry is finished so go to next field or click the
> OK button or something.
>
> TextBox1_KeyPress uses KeyAscii, the resulting ascii value for the entry,
> where A is 65 and a is 97. For testing and development I've found that
> putting the value in question up in the form caption and/or in the immediate
> window is helpful:
>
> Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
> Me.Caption = KeyAscii
> Debug.Print KeyAscii
> End Sub
>
> HTH. Best wishes Harald
>
> "Jon Peltier" <(E-Mail Removed)> skrev i melding
> news:(E-Mail Removed)...
>
>>Well, isn't this nice. This morning I was wondering how I was going to
>>tell whether the user typed or pasted his information into the textbox!
>>Thanks Dave and Harald.
>>
>>(An additional coincidence is the OP's handle, given that my real
>>technical training is in metallurgy.)
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>http://PeltierTech.com/Excel/Charts/
>>_______
>>
>>Dave Peterson wrote:
>>
>>
>>>Try adding this to a test form:
>>>
>>>Option Explicit
>>>Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
>>> ByVal Shift As Integer)
>>> MsgBox KeyCode
>>>End Sub
>>>
>>>And you'll see what each keystroke represents.
>>>(hit ctrl and don't let go. Then hit V and you'll see the 86.)
>>>
>>>You can search VBA's help for "keycode constants" and see vbKeyV and how

>
> it
>
>>>represents 86.
>>>
>>>
>>>
>>>"thesteelmaker <" wrote:
>>>
>>>
>>>>Thank you very much.
>>>>
>>>>I'm very impressed.
>>>>
>>>>I've not come across any reference to keycodes, or anything in you
>>>>coding.
>>>>
>>>>Could you explain a bit more.
>>>>
>>>>Thanks.
>>>>
>>>>Private Sub TextBox1_KeyDown(ByVal KeyCode As _
>>>>MSForms.ReturnInteger, ByVal Shift As Integer)
>>>>If Shift = 2 Then
>>>>If KeyCode = 86 Then KeyCode = 0
>>>>End If
>>>>End Sub
>>>>
>>>>Private Sub TextBox1_KeyPress(ByVal KeyAscii _
>>>>As MSForms.ReturnInteger)
>>>>Select Case KeyAscii
>>>>Case 46
>>>>If InStr(TextBox1.Text, ".") > 0 Then _
>>>>KeyAscii = 0
>>>>Case 48 To 57
>>>>Case Else
>>>>KeyAscii = 0
>>>>End Select
>>>>End Sub
>>>>
>>>>---
>>>>Message posted from http://www.ExcelForum.com/
>>>
>>>

>
>


 
Reply With Quote
 
Dave Peterson
Guest
Posts: n/a
 
      23rd Mar 2004
Until Harald returns, you could review Chip Pearson's site:
http://www.cpearson.com/excel/clipboar.htm



Jon Peltier wrote:
>
> Harald old pal -
>
> How do you read the clipboard content? I was letting the paste occur
> into a textbox, then I parsed that to turn garbage into data. But
> reading the clipboard content sounds much more elegant.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> http://PeltierTech.com/Excel/Charts/
> _______
>
> Harald Staff wrote:
>
> > Hi Guys
> >
> > Just adding a few minor things.
> >
> > Jon: The paste code is pretty raw as you noticed. My real life code for
> > those events is
> > - cancel
> > - read the clipboard content
> > - if acceptable then paste it in, or paste the "acceptable part" in
> > but it's pretty large and confusing, so I added the cancel part only for
> > demonstration.
> >
> > Steelmaker: Many built-in events provide variables that it's code can
> > validate and change.For example in ThisWorkbook:
> >
> > Private Sub Workbook_BeforeSave(ByVal _
> > SaveAsUI As Boolean, Cancel As Boolean)
> > Cancel = True
> > End Sub
> >
> > Now this workbook will never save because we cancel it. Not useful as is,
> > but add a condition or two to see if the save location is ok, if this and
> > that is completely filled out, ...
> >
> > TextBox1_KeyDown uses Keycode, the number of the keyboard key pressed. "a"
> > and "A" is the same key, number 65, the diference is Shift, which is 0 for a
> > and 1 for A. Ctrl is 2. A very useful keycode to trap is 13, the Enter key,
> > usually meaning that the entry is finished so go to next field or click the
> > OK button or something.
> >
> > TextBox1_KeyPress uses KeyAscii, the resulting ascii value for the entry,
> > where A is 65 and a is 97. For testing and development I've found that
> > putting the value in question up in the form caption and/or in the immediate
> > window is helpful:
> >
> > Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
> > Me.Caption = KeyAscii
> > Debug.Print KeyAscii
> > End Sub
> >
> > HTH. Best wishes Harald
> >
> > "Jon Peltier" <(E-Mail Removed)> skrev i melding
> > news:(E-Mail Removed)...
> >
> >>Well, isn't this nice. This morning I was wondering how I was going to
> >>tell whether the user typed or pasted his information into the textbox!
> >>Thanks Dave and Harald.
> >>
> >>(An additional coincidence is the OP's handle, given that my real
> >>technical training is in metallurgy.)
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>http://PeltierTech.com/Excel/Charts/
> >>_______
> >>
> >>Dave Peterson wrote:
> >>
> >>
> >>>Try adding this to a test form:
> >>>
> >>>Option Explicit
> >>>Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
> >>> ByVal Shift As Integer)
> >>> MsgBox KeyCode
> >>>End Sub
> >>>
> >>>And you'll see what each keystroke represents.
> >>>(hit ctrl and don't let go. Then hit V and you'll see the 86.)
> >>>
> >>>You can search VBA's help for "keycode constants" and see vbKeyV and how

> >
> > it
> >
> >>>represents 86.
> >>>
> >>>
> >>>
> >>>"thesteelmaker <" wrote:
> >>>
> >>>
> >>>>Thank you very much.
> >>>>
> >>>>I'm very impressed.
> >>>>
> >>>>I've not come across any reference to keycodes, or anything in you
> >>>>coding.
> >>>>
> >>>>Could you explain a bit more.
> >>>>
> >>>>Thanks.
> >>>>
> >>>>Private Sub TextBox1_KeyDown(ByVal KeyCode As _
> >>>>MSForms.ReturnInteger, ByVal Shift As Integer)
> >>>>If Shift = 2 Then
> >>>>If KeyCode = 86 Then KeyCode = 0
> >>>>End If
> >>>>End Sub
> >>>>
> >>>>Private Sub TextBox1_KeyPress(ByVal KeyAscii _
> >>>>As MSForms.ReturnInteger)
> >>>>Select Case KeyAscii
> >>>>Case 46
> >>>>If InStr(TextBox1.Text, ".") > 0 Then _
> >>>>KeyAscii = 0
> >>>>Case 48 To 57
> >>>>Case Else
> >>>>KeyAscii = 0
> >>>>End Select
> >>>>End Sub
> >>>>
> >>>>---
> >>>>Message posted from http://www.ExcelForum.com/
> >>>
> >>>

> >
> >


--

Dave Peterson
(E-Mail Removed)
 
Reply With Quote
 
Jon Peltier
Guest
Posts: n/a
 
      23rd Mar 2004
Thanks, Dave. I thought it was going to be worse than that, API calls
and the like. Also, thanks, Chip. I should have thought of visiting this
online encyclopedia.

- Jon

Dave Peterson wrote:

> Until Harald returns, you could review Chip Pearson's site:
> http://www.cpearson.com/excel/clipboar.htm
>
>
>
> Jon Peltier wrote:
>
>>Harald old pal -
>>
>>How do you read the clipboard content? I was letting the paste occur
>>into a textbox, then I parsed that to turn garbage into data. But
>>reading the clipboard content sounds much more elegant.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>http://PeltierTech.com/Excel/Charts/
>>_______
>>
>>Harald Staff wrote:
>>
>>
>>>Hi Guys
>>>
>>>Just adding a few minor things.
>>>
>>>Jon: The paste code is pretty raw as you noticed. My real life code for
>>>those events is
>>>- cancel
>>>- read the clipboard content
>>>- if acceptable then paste it in, or paste the "acceptable part" in
>>>but it's pretty large and confusing, so I added the cancel part only for
>>>demonstration.
>>>
>>>Steelmaker: Many built-in events provide variables that it's code can
>>>validate and change.For example in ThisWorkbook:
>>>
>>>Private Sub Workbook_BeforeSave(ByVal _
>>> SaveAsUI As Boolean, Cancel As Boolean)
>>>Cancel = True
>>>End Sub
>>>
>>>Now this workbook will never save because we cancel it. Not useful as is,
>>>but add a condition or two to see if the save location is ok, if this and
>>>that is completely filled out, ...
>>>
>>>TextBox1_KeyDown uses Keycode, the number of the keyboard key pressed. "a"
>>>and "A" is the same key, number 65, the diference is Shift, which is 0 for a
>>>and 1 for A. Ctrl is 2. A very useful keycode to trap is 13, the Enter key,
>>>usually meaning that the entry is finished so go to next field or click the
>>>OK button or something.
>>>
>>>TextBox1_KeyPress uses KeyAscii, the resulting ascii value for the entry,
>>>where A is 65 and a is 97. For testing and development I've found that
>>>putting the value in question up in the form caption and/or in the immediate
>>>window is helpful:
>>>
>>>Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
>>>Me.Caption = KeyAscii
>>>Debug.Print KeyAscii
>>>End Sub
>>>
>>>HTH. Best wishes Harald
>>>
>>>"Jon Peltier" <(E-Mail Removed)> skrev i melding
>>>news:(E-Mail Removed)...
>>>
>>>
>>>>Well, isn't this nice. This morning I was wondering how I was going to
>>>>tell whether the user typed or pasted his information into the textbox!
>>>>Thanks Dave and Harald.
>>>>
>>>>(An additional coincidence is the OP's handle, given that my real
>>>>technical training is in metallurgy.)
>>>>
>>>>- Jon
>>>>-------
>>>>Jon Peltier, Microsoft Excel MVP
>>>>Peltier Technical Services
>>>>http://PeltierTech.com/Excel/Charts/
>>>>_______
>>>>
>>>>Dave Peterson wrote:
>>>>
>>>>
>>>>
>>>>>Try adding this to a test form:
>>>>>
>>>>>Option Explicit
>>>>>Private Sub UserForm_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
>>>>> ByVal Shift As Integer)
>>>>> MsgBox KeyCode
>>>>>End Sub
>>>>>
>>>>>And you'll see what each keystroke represents.
>>>>>(hit ctrl and don't let go. Then hit V and you'll see the 86.)
>>>>>
>>>>>You can search VBA's help for "keycode constants" and see vbKeyV and how
>>>
>>>it
>>>
>>>
>>>>>represents 86.
>>>>>
>>>>>
>>>>>
>>>>>"thesteelmaker <" wrote:
>>>>>
>>>>>
>>>>>
>>>>>>Thank you very much.
>>>>>>
>>>>>>I'm very impressed.
>>>>>>
>>>>>>I've not come across any reference to keycodes, or anything in you
>>>>>>coding.
>>>>>>
>>>>>>Could you explain a bit more.
>>>>>>
>>>>>>Thanks.
>>>>>>
>>>>>>Private Sub TextBox1_KeyDown(ByVal KeyCode As _
>>>>>>MSForms.ReturnInteger, ByVal Shift As Integer)
>>>>>>If Shift = 2 Then
>>>>>>If KeyCode = 86 Then KeyCode = 0
>>>>>>End If
>>>>>>End Sub
>>>>>>
>>>>>>Private Sub TextBox1_KeyPress(ByVal KeyAscii _
>>>>>>As MSForms.ReturnInteger)
>>>>>>Select Case KeyAscii
>>>>>>Case 46
>>>>>>If InStr(TextBox1.Text, ".") > 0 Then _
>>>>>>KeyAscii = 0
>>>>>>Case 48 To 57
>>>>>>Case Else
>>>>>>KeyAscii = 0
>>>>>>End Select
>>>>>>End Sub
>>>>>>
>>>>>>---
>>>>>>Message posted from http://www.ExcelForum.com/
>>>>>
>>>>>
>>>

>


 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Userform Textbox to Worksheet Textbox targante Microsoft Excel Programming 1 2nd May 2009 12:26 AM
fill userform textbox from userform listbox clik event GregJG Microsoft Excel Programming 1 7th Dec 2008 03:05 PM
Excel VBA - Userform textbox formatting thesteelmaker Microsoft Excel Programming 1 3rd Mar 2004 09:51 PM
Excel VBA - Userform Checkbox/Textbox Problem Thunder5 Microsoft Excel Programming 1 16th Feb 2004 03:06 PM
Re: UserForm TextBox to ActiveSheet TextBox over 256 characters Dan E Microsoft Excel Programming 1 28th Jul 2003 07:36 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 05:39 AM.