Search This Blog

Friday 20 July 2012

CLOSE USERFORM ON ESCAPE KEY


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:)

15 comments:

  1. Thanks! Just what I was looking for. : )

    ReplyDelete
  2. Too Cool, very simple i was waiting for this code from a long time. Thank u

    ReplyDelete
  3. I 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.

    Please 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.

    ReplyDelete
    Replies
    1. Hi,

      PFB 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!

      Delete
    2. 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 !!

      Delete
    3. If Not Matchvalue = 0 Then
      .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

      Delete
    4. 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 !!

      Delete
  4. Thanks to all who liked my post.

    ReplyDelete
  5. HI I want to use 2 VBA code as seen below in same one sheet, how can i do this , please advice !!

    First 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

    ReplyDelete
  6. Thanks 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...

    ReplyDelete
    Replies
    1. Sorry for the bad question... Got it resolved.

      Delete
  7. NOT SOLVED............ as After Eevry Entry User Form will auto close that is completely unwanted.

    ReplyDelete
  8. My problem has been solved.
    Thanks a lot!!!

    ReplyDelete
  9. Excellent.
    you are so helpful

    ReplyDelete