Put a commandbutton on your userform and write the below code on command button Click event.
Private Sub CommandButton1_Click()
Unload Me
End Sub
Then set cancel to true in its property. See image below(Click on the image to enlarge it)
Now when you run userform and press escape key on your keyboard, userfrom will be closed.
Is the post useful? Please post your comment and follow my blog:)
Is the post useful? Please post your comment and follow my blog:)
Thanks! Just what I was looking for. : )
ReplyDeleteToo Cool, very simple i was waiting for this code from a long time. Thank u
ReplyDeleteI am trying to use range find to look up a value within a column, and return the matching value from the next column from another sheet.
ReplyDeletePlease help with Vlookup VBA code.
I use Vlookup function currently i.e =VLOOKUP($A1326*1,'G90'!$A$1:$A$2000,1,0)
Suppose I have values in Sheet1 A column and it want to check if the same matching figures are in any column in sheet2 has these matching figures then should give me same matching figure along with same rows correspondence figures in sheet1 B column. like vlookup does but i need macro VBA code.
Hi,
DeletePFB the code. Please change the sheet name accordingly. I assumed that your data is in Sheet1 in column A & B and it is column B in Sheet2 where you are trying to fetch the matched values.
Private Sub CommandButton1_Click()
Dim rng As Range
Dim sht1, sht2 As Worksheet
Dim i, Matchvalue As Integer
Set sht1 = ThisWorkbook.Sheets(1)
Set sht2 = ThisWorkbook.Sheets(2)
lr = sht1.Range("A65536").End(xlUp).Row
Set rng = sht1.Range("A1:A" & lr)
With sht2
lr = .Range("A65536").End(xlUp).Row
For i = 2 To lr
On Error Resume Next
Matchvalue = Application.Match(.Cells(i, 1), rng, 0)
If Not Matchvalue = 0 Then
.Cells(i, 1).Offset(0, 1).Value = rng.Cells(Matchvalue, 1).Offset(0, 1).Value
Else
.Cells(i, 1).Offset(0, 1).Value = "NA"
End If
Matchvalue = 0
Next
End With
End Sub
Please subscribe if you liked my blog:). Thank you!
Thank You for reply this gives answer in column B i want answer in another column say column D then where to do the changes in VBA code , please advice !!
DeleteIf Not Matchvalue = 0 Then
Delete.Cells(i, 1).Offset(0, 3).Value = rng.Cells(Matchvalue, 1).Offset(0, 1).Value
Else
.Cells(i, 1).Offset(0, 3).Value = "NA"
End If
Thank You Ma'am appreciate your quick response one more question above code gives matching values of 1 column only if i want matching values of 2 columns then !!
DeleteThanks to all who liked my post.
ReplyDeleteHI I want to use 2 VBA code as seen below in same one sheet, how can i do this , please advice !!
ReplyDeleteFirst Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
FontSize = ActiveCell.Font.Size
LargeSize = FontSize * 1.6
Cells.Font.Size = FontSize
ActiveCell.Font.Size = LargeSize
End Sub
Second Code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
On Error Resume Next
With Target
If .Count = 1 Then
str = .Address & "," & .Row & ":" & .Row _
& "," & Left(.Address, InStr(2, .Address, "$") - 1) & ":" _
& Left(.Address, InStr(2, .Address, "$") - 1)
End If
End With
Range(str).Select
On Error GoTo 0
End Sub
Thank you very much
ReplyDeleteThanks it worked.. But how can it be exlplained that if I change the name propert of the command button (with onClick code of "unload me") pressing Escape key does not unload the form. Escape key only works if the command button's name is "CommandButton1".. Weird...
ReplyDeleteSorry for the bad question... Got it resolved.
DeleteNOT SOLVED............ as After Eevry Entry User Form will auto close that is completely unwanted.
ReplyDeleteMy problem has been solved.
ReplyDeleteThanks a lot!!!
Excellent.
ReplyDeleteyou are so helpful