Results 1 to 1 of 1

Bring Focus to VBscript Window

Threaded View

vs604 Bring Focus to VBscript Window 08-10-2012, 09:06 PM
  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1

    Bring Focus to VBscript Window

    Hey guys,

    My friend wanted a program for his employees that would allow them to input some data. The program would then just search an excel file for matches. If a match was found, then a message would be displayed that said "go ahead and do it." If a match wasn't found, then it would say "don't do it." Also, the employees have access to a user account on the PC that is restricted. So he wanted a desktop icon that would only run the macro, and not bring up the actual excel file. The VBA script in excel sheet and the VBScript work fine. The only problem I'm running into is when I double click the icon on the desktop (which runs the macro), the focus is not on the search window that comes up. I have to click on it to input anything. When I input something, and click "Okay," the window loses focus again, and I have to click back on it. I want it to have focus when it is ran, and to maintain focus until I click somewhere else. I would greatly appreciate any additions to the code you guys can suggest. Thank you in advance.

    Script for the macro in the workbook:

    Public Sub FindText()
    
    Dim ws As Worksheet, Found As Range
    Dim myText As String, FirstAddress As String
    Dim AddressStr As String, foundNum As Integer
    
    myAgain:
    myText = ""
    FirstAddress = ""
    foundNum = 0
    rngNm = ""
    AddressStr = ""
    thisLoc = ""
    myF = ""
    myRD = ""
    
    myText = InputBox("Enter Full Name of Company")
    
    If myText = "" Then Exit Sub
    
    For Each ws In ThisWorkbook.Worksheets
    With ws
    Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)
    
    If Not Found Is Nothing Then
    FirstAddress = Found.Address
    Do
    foundNum = foundNum + 1
    AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
    Set Found = .UsedRange.FindNext(Found)
    
    Loop While Not Found Is Nothing And Found.Address <> FirstAddress
    End If
    End With
    Next ws
    
    If Len(AddressStr) Then
    MsgBox "You can cash it."
    GoTo myAgain
    Else:
    MsgBox "WAIT! Check with boss or don't cash it.", vbExclamation
    GoTo myAgain
    End If
    
    End Sub

    Code that I pasted to Notepad and saved as .vba in order to make a desktop icon to launch the macro:


    Dim XL
    Dim WB
    Set XL = CreateObject("Excel.Application")
    Set WB = XL.Workbooks.Open("C:\Users\My Name\Desktop\ExcelWorkbook.xlsm")
    XL.Run "FindText"
    WB.Close
    XL.Quit
    Set WB = Nothing
    Set XL = Nothing
    Last edited by vs604; 08-12-2012 at 06:58 PM.

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1