Sharid
Well-known Member
- Joined
- Apr 22, 2007
- Messages
- 1,066
- Office Version
- 2016
- Platform
- Windows
My following code was working, but now I am having a few issues, When the textbox is selected it pasted the data from the clipboard. If it is NOT selected then it gives error messages advising the user to either select the textbox or there is nothing in the clipboard to paste.
In the textbox I had this code, to ensure if it was NOT selected then it would fireup a message for the user.
Changes I was making
I replaced the msgbox with small userform that look a bit more professional, problem is the code does not behave the same with the userforms as it did with the message boxes
Problems
I have left the original message in the code, they are commented out all I have done is changed the msg box to userform.
These are the two userforms that I am using as messages, both popup and message 2 is on top of message 2, I have placed them next to each other for your viewing.
Original thread My post on Mr Excel
In the textbox I had this code, to ensure if it was NOT selected then it would fireup a message for the user.
VBA Code:
Private Sub PasteTextBox_Enter ()
isSelected = True
End Sub
Changes I was making
I replaced the msgbox with small userform that look a bit more professional, problem is the code does not behave the same with the userforms as it did with the message boxes
Problems
- When the textbox is selected, it keeps firing up the message that the textbox is NOT selected, this NOW happens if I use the message box process OR userform messages.
- When the code is run it shows all the userform messages. Originally with the message box it would only show the relevant message.
I have left the original message in the code, they are commented out all I have done is changed the msg box to userform.
VBA Code:
Private Sub PasteBt_Click()
'''Paste to sheet9 from textbox 'Pastes from ClipBoard
Dim objdataobject As MSForms.DataObject
Set objdataobject = New MSForms.DataObject
Dim Str As String, a, i As Long, msg As String
Dim cnt As Integer
Dim w()
'standard message
'msg = "Your Copied Data Has Been Pasted Now Click then Start button"
PasteSucess.Show 'using userforms as messages
If Not isSelected Then
'msg = "**** INFO **** You have NOT selected the Paste box **** INFO **** "
PasteMessage.Show 'using userforms as messages
Else
objdataobject.GetFromClipboard
If Me.PasteTextBox.Value = "" Then
On Error Resume Next
Me.PasteTextBox.Value = objdataobject.GetText
Str = Replace(PasteTextBox.Value, Chr(13), "")
a = Chr(10)
cnt = UBound(Split(Str, a))
If Len(Str) < 5 Then
'msg = "* ERROR * You need to copy some Data in order to Paste * ERROR *"
NothingToPaste.Show 'using userforms as messages
Else
On Error Resume Next
ReDim w(1 To cnt + 1, 1 To 1)
For i = 0 To cnt
w(i + 1, 1) = Replace(Split(Str, Chr(10))(i), Chr(13), "")
'w(i + 1, 1) = Split(Str, Chr(10))(i)
Next i
Sheet9.Range("A2").Resize(i, 1) = w
End If
PasteTextBox.Value = ""
End If
End If
isSelected = False
'MsgBox msg
PasteMessage.Show 'using userforms as messages
End Sub
These are the two userforms that I am using as messages, both popup and message 2 is on top of message 2, I have placed them next to each other for your viewing.
Original thread My post on Mr Excel