Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

Results 1 to 3 of 3

Thread: search box

  1. #1
    Join Date
    2nd January 2017

    search box


    i am trying to write a macro that will search for a value entered by a user in a textbox and click for search in the sheet1 if it founds a value then copy entire row and paste it to sheet2 of same workbook. Also it should validate if user inputs a wrong or blank .

    Please help

  2. #2
    Join Date
    10th January 2007

    Re: search box

    Hi, welcome to Ozgrid.

    You have posted this in the Hire Help Forum. If you want somebody to provide you with a completed, working solution then you need to edit your thread title to state the amount you are offering to pay for the solution. 10% of that amount needs to be paid to Ozgrid ([email protected]) immediately, and 90% to whoever provides the solution when the solution is ready.

    If you just need help to point you in the right direction for you to solve this yourself then reply here and I will move this thread to the appropriate forum.
    We now have a reputation system in place. It can be found on the 'Star' icon on the bottom left hand side of the post

  3. #3
    Join Date
    2nd January 2017

    Re: search box


    i want to send a range of data from current workbook to a temp workbook and send to a particular email id. Code is working fine but i want that for the email - To list, subject and Body all these should be selected from the second tab (Configuration Sheet). but i am not getting that

    here is my code please help

    Sub Mail_Range()

    Dim Source As Range
    Dim Dest As Workbook
    Dim wb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim OutApp As Object
    Dim OutMail As Object
    Set Source = Nothing
    On Error Resume Next
    Set Source = Range("A4:L50").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Source Is Nothing Then
    MsgBox "The source is not a range or the sheet is protected, " & "please correct and try again.", vbOKOnly
    Exit Sub
    End If
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With
    Set wb = ActiveWorkbook
    Set Dest = Workbooks.Add(xlWBATWorksheet)
    With Dest.Sheets(1)
    .Cells(1).PasteSpecial Paste:=8
    .Cells(1).PasteSpecial Paste:=xlPasteValues
    .Cells(1).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    End With
    TempFilePath = Environ$("temp") & ""
    TempFileName = "Details of " & wb.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
    If Val(Application.Version) < 12 Then
    'You use Excel 2000-2003
    FileExtStr = ".xls": FileFormatNum = -4143
    'You use Excel 2007-2010
    FileExtStr = ".xlsx": FileFormatNum = 51
    End If

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With Dest
    .SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
    On Error Resume Next

    With OutMail

    Sheets("Configuration Sheet").Select

    .To = Sheets("Configuration Sheet").Range("C2").Value

    .CC = ""
    .BCC = ""
    .Subject = Sheets("Configuration Sheet").Range("E2").Value

    .Body = Sheets("Configuration Sheet").Range("D2").Value

    '.Body = "Transport Team," & vbNewLine & "Can you please help to provide adhoc drop/pickup for associates.Details are given in the attached sheet."

    .Attachments.Add Dest.FullName


    End With
    On Error GoTo 0
    .Close SaveChanges:=False
    End With
    Kill TempFilePath & TempFileName & FileExtStr
    Set OutMail = Nothing
    Set OutApp = Nothing
    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With

    MsgBox "Email has been sent successfully"

    End Sub

    Please help asap. thanks

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 10
    Last Post: September 9th, 2014, 23:47
  2. Replies: 6
    Last Post: May 8th, 2014, 10:42
  3. Search box in Excel to search key words in sentences in multiple worksheets
    By fernandoii676 in forum Excel and/or Access Help
    Replies: 1
    Last Post: October 20th, 2012, 00:50
  4. Replies: 1
    Last Post: October 19th, 2012, 23:56
  5. Replies: 3
    Last Post: May 25th, 2005, 03:45


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts