Macro - to prevent user to paste in the textbox

[Dhamo]

Hi,


In my user form, i ll get input of file name from the user, and i prevented typing ambitious characters (*<>/). I added code, when textbox value is changed the code will check the key pressed is any of these spl char, it will check the ascii values and will not allow the value to be typed in the textbox.


But there is another option, user can paste those chars. So pls help me in preventing the same

 

[Luke M]

After user inputs their string, I'm assuming they'll hit some sort of "ok" or "confirm" button. You could then check the text string character by character for special characters and cause the code to reject/accept based on that.

 

[Dhamo]

Thanks Luke, But I used 'Textbox1.change'. So there is no possibility to prevent the user to paste?

 

[Luke M]

No, you can't detect a paste, unfortunately. That's why I suggested doing the check after they hit some sort of confirmation button.

 

[SirJB7]

Hi, Dhamo!


May I suggest doing this?


Set two code events, one for Change event and other for KeyUp event. Look at this example:


-----

[pre]

Option Explicit

Private Sub TextBox1_Change()
Debug.Print Now, TextBox1.Value
End Sub

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Debug.Print Now; Shift; KeyCode; Chr$(KeyCode)
End Sub

[/pre]
-----


Arrange VBE window to get Immediante pane displayed without being overlapped by user form and check what happens.

- Each time you type a character, the first event triggered is Change, and in the sample code it only displays the control value, but you can play around with its new value and a previously manually saved old value (sorry but here are no OldValue properties available). Then the KeyUp event is triggered and the sample code displays the event parameters and the actual character typed.

- Each time you paste something into the control box, same things happen with the exception that there are 2 KeyUp events triggered: first one, Shift=2 (Control) and KeyCode=86 (V), second one, Shift=0 and KeyCode=17.


You should check for Shift=2 and KeyCode=86 to detect Ctrl-V (or mouse paste, they're equally registered) and then proceed to:

a) restore the previously control value saved at the end of Change event (without considering paste actions, just an additional If only)

b) optionally display warnings via MsgBox or other user form or anything you might want.


I hope I've been clear enough. If not, just advise if any issue.


Regards!

 

[SirJB7]

@Luke M

Hi!

I didn't know if that could be done and I never tried it before. It was your so sure "No, ..." that triggered my curiosity.

Regards!

PS: Remember the old group of the 80's Curiosity Killed The Cat? I didn't, until I checked among the things my grand father left me in his will...