Bring Focus to Input Box

vs604

New Member
Joined
Aug 13, 2012
Messages
2
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 (because he doesn't want them having access to any of that). The VBA script in the excel sheet (which I actually got from this forum) and the VBScript work fine. When I double click the icon on the desktop (which runs the macro), the search box comes up, which is what I want. The problem I run into is the search box does not automatically have focus. 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. Any help you guys can give to try and fix this would be greatly appreciated.

VBA Script:
Code:
Public Sub FindText()'Run from standard module, like: Module1.


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


VBScript (saved in notepad as .vbs and used as an icon):

Code:
Dim XLDim WB
Set XL = CreateObject("Excel.Application")
Set WB = XL.Workbooks.Open("C:\Users\Bhavani Prasad\Desktop\Check_Cash_Customers.xlsm")
XL.Run "FindText"
WB.Close
XL.Quit
Set WB = Nothing
Set XL = Nothing

 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top