Get pop up message box when text character limit exceeded

Bradon

Board Regular
Joined
Dec 18, 2002
Messages
66
I want to limit the number of characters a user can type into a memo field to 500.

The validation rule is set to Len([output1])<500 with an appropriate validation text set.

When the validation rule is breached pop up message box only appears after the user tries to move to another field.

Is there a way of getting the message to display immediately the limit has been reached, or stopping further entry of text?
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Stubby Holder

New Member
Joined
Jul 29, 2003
Messages
26
Re: Get pop up message box when text character limit exceede

hi, Bradon,
try this:

Code:
Private Sub txtYourMemoFieldName_KeyPress(KeyAscii As Integer)

Select Case Me.txtYourMemoFieldName.SelStart
    Case 0 To 500
    Case Else
        Select Case KeyAscii
          Case Is >= 32
                KeyAscii = 0
                'Beep
                MsgBox "Text limit reached!"
          Case Else
        End Select
    End Select
End Sub

Remove the comment marker before "Beep" for an audible warning as well.

HTH
 

Bradon

Board Regular
Joined
Dec 18, 2002
Messages
66
Re: Get pop up message box when text character limit exceede

Thanks Stubby,

Dare I ask "Where" the code goes - I didn't recognize any location from it?

But then I am English!!!!
 

Stubby Holder

New Member
Joined
Jul 29, 2003
Messages
26
Re: Get pop up message box when text character limit exceede

Sorry, half asleep!

I am presuming the data is entered from a form..

Open the form in design, select your memo field text box.
On the Event tab, click the end of the line for "on Key Press"
Click the three dots
Choose code builder, OK
Paste this bit in between the Private Sub... & End Sub lines

Code:
Select Case Me.txtYourMemoFieldName.SelStart 
    Case 0 To 500 
    Case Else 
        Select Case KeyAscii 
          Case Is >= 32 
                KeyAscii = 0 
                'Beep 
                MsgBox "Text limit reached!" 
          Case Else 
        End Select 
End Select

Change 'txtYourMemoFieldName' to the name of the field.

If the data isn't entered from a form, you should consider creating one, to give you full control over the data entry.

Stubby
 

Bradon

Board Regular
Joined
Dec 18, 2002
Messages
66
Re: Get pop up message box when text character limit exceede

Thanks Stubby,

It worked fine - That should put paid to people writing too much for the available report space!

Gordon (y)
 

Forum statistics

Threads
1,148,050
Messages
5,744,513
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top