Desktop VBS command to open an input box and then open an excel sheet and find the input.

Hi All, I would like to create a Vbs script and have it present an input box to the user and then open a set excel document, and go to a set TAB and then go to the a specific cell in relation to the input.

I started with the below script which opens the excel spreadsheet and takes me to sheet 2 and cell R1820 but would like to add a prompt for the user to enter a part number which would then open the sheet and go to the date cell related to the user input. The user input would locate the part number in column B and then make active the cell in the same row at column R.


Set objXl = CreateObject("Excel.Application")
Set ObjWB = objXl.Workbooks.Open("\\MSSLGBDC01\MSSLGBUserData\Shared Project Data\Project Tracking.xlsm")
objXl.Goto ObjWB.Sheets(2).Range("r1820")
objXl.Visible = True

I have seen the following script used in a similar way but I am unable to adapt it. Any help would be greatly appreciated.

Application.Dialogs(xlDialogFormulaFind).Show

regards Russ

Does this help?

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

Does this help?

Please Login or Register  to view this content.

I get an error if i cut and paste this. the error is line 6, char 7 expected end of statement.

Originally Posted by JOHN H. DAVIS

Does this help?

Please Login or Register  to view this content.

Also i dont need the range ("r1820") part only need it to execute the find for sheet 2

Maybe:

Please Login or Register  to view this content.

Hi John, still get same error. i googled it and think possibly the DIM X As range is somehow incorrect.?

I am cutting and pasting into a dektop shortcut created by renaming a text file and saving as .vbs.

if i change the lines to

Dim x
x = string
dim y
y = string

it moves past this
to line 11, char 34 expected ')'

Anyone got any answers on this, would really appreciate it.
Thanks for your efforts thus far John.

Thanks Norie, when i run this I get an error message after i enter the part number.
line 10 char 2 subscript out of range.

I will try fathom it myself but if any thing is obvious to you I would as usually appreciate your help.

thanks

Hi, if i have renamed my tabs, for example sheet 2 is "inspection" would the following line of code have to have sheet2 references changed to inspection?

Yes it seems so as i have updated and now moved on to next problem :-)
Now getting part number not found issue but will try resolve myself

Hi Norie, I spotted that error and changed. I am using the following code so far and it working other than it is not finding the part number. It comes back with part number not found even though it is in the sheet.
I believe it may have something to do with the range? I am unsure what (rnfnd.row, 18) is in there for as looks like this is restricting the search to only items in row 18?
Regarding the workbook open event, I dont know is the honest answer. I am simply looking at questions in forums that loosely relate to what I require. I am then taking lines that look like they would work for me (I am not even at the novice stage) and with a little trial and error I have reached this stage. With a lot of help from guys like you also.
I will try your alternative suggestion now.

Hi Norie, I spotted that error and changed. I am using the following code so far and it working other than it is not finding the part number. It comes back with part number not found even though it is in the sheet.
I believe it may have something to do with the range? I am unsure what (rnfnd.row, 18) is in there for as looks like this is restricting the search to only items in row 18?
Regarding the workbook open event, I dont know is the honest answer. I am simply looking at questions in forums that loosely relate to what I require. I am then taking lines that look like they would work for me (I am not even at the novice stage) and with a little trial and error I have reached this stage. With a lot of help from guys like you also.
I will try your alternative suggestion now....
Just tried and get same part number not found result.

Originally Posted by Norie

There's actually a typo in the code, rnFnd.Row should be rngFnd.Row.

Not sure if that's the cause of the error though but try changing it.

If fixing that makes no difference then using Application.Match instead of Find might be an option.

Not 100% sure how to handle the part number not being found if that was used though.

It might look something like this.

Please Login or Register  to view this content.

By the way, is there a reason you aren't using the workbook open event?

In this one I get the error line 12, char 5, object required: application

Should i change application in the script to objxl?
I have tried this and the error does not come up but i still dont get a result.
what does the part cells(res, 18) do?

thanks

ok, still no result lol. I think previously the work boo opened on the correct tab (sheet 2) but is opening only on sheet 1 now.

I am not familiar with VBS so I used other scripts and altered them to suit.
The work book open event does not mean a great deal to me, forgive my ignorance.
If you think that would make a difference then I would be happy for you to suggest the applicable script?

Hi Norie, many thanks for your continued help on this one.Hopefully you will find attached a spreadsheet project tracking.
I have replaced most of the data with dummy data and deleted sheets and columns that have no relevance to the task in hand.

The basic idea is to provide department 2 with a short cut they can simply click on which will open up the attached spreadsheet.
The VBS shortcut will as you already know request a part number, the spreadsheet should then open up on sheet 2 (ISIR) and go to the cell "date internal" for the entered part number. The user will then simply add a date, which will then be also duplicated in the Engineering sheet.

This is my immediate temporary requirement.

Once this is working I would then look to have the original VBS shortcut not only ask for the part number but then ask for date.
This would automatically put in the date on the spreadsheet with out the user having to see the sheet open.
It would be ideal if this would add the date to the required cell and save and close the spreadsheet.
The spreadsheet is to update projects so is saved on the server with users having shortcuts on the desktops.
When a user opens the spreadsheet and another user is already using it they obviously are alerted and can open as read only.

You will see that the excel spreadsheet is not created by an expert (that would be myself) but it serves its purpose.

Your help as always appreciated.

Hmmm I cant actually see the attachment, I did successfully upload it though.

3rd time lucky?

Hi, the shortcut is for the desktop as did not want to have macro's enabled in spreadsheet.
The code was going into a text file and being saved as VBS and saved on the desktop.
The particular users this is aimed at will only ever use this spreadsheet for this one task.
Therefore I wanted it to be as easy as possible. I thought clicking the shortcut, entering the number and the date was as easy as it could get for them.
If its within the spreadsheet as you have supplied then they will have to open the spreadsheet, then run the macro which is ab extra step.

Originally Posted by Norie

The code I added to the workbook is automatically executed when the workbook is opened.

It's basically exactly the same as the VBS code I posted earlier.

This is one of the problems. I have 5 engineers updating this sheet on a daily basis, they do not update this particular sheet (ISIR). They dont want this to pop up, its only members of another department, therefore we will open up the book from a standard direct shortcut as we do now and the other users would use the VBS desktop script which would give them the input option.
I am aware that to add a macro button to the ISIR page would be simpler but we often have issues with security and macros not being enabled etc.
I just liked the idea of the not technical persons using this spreadsheet having one shortcut to click on and that was it.
As i said my ideal would be for the input box to ask the part number and then the date adn for it to automatically add it to the correct cell and save the spreadsheet :-)

Hi Norie, yes apologies I did try it. Again it returns the part number not found message.
It does open the workbook but when it opens it is in the engineering tab (sheet1).

Ahhhh, I just tried entering as a part number T* and it worked, that is to say it opened the work book on page 2 and correctly choose the cell required for the first instance of a part number beginning with T
Let me investigate further as the finish line is close i feel.

I am trying it on the actual work book rather than the one I uploaded. There should be no difference as I simple deleted some columns and added dummy data. It is working with some part number I enter but not all. I will test on the upload sheet to see it that gives 100% and if so the fault may be with the format of the added text in certain cells, although it shouldnt make a difference.
thanks again

Part number are a mix.
Its working fine in the test sheet so the issue is with the format of the original sheet.
I will go through and check which part numbers do not show up (return not found).
I did use the Find (Ctrl F) search in the sheet for the ones which did not work and they were detected by the find function in the sheet.
I will cut and paste the ones that return not found into a sheet and try discover the link.

The format of the cells for part number is [I]general[I] as the part number is a mix.
I think you are correct as it will find part numbers with letters or a mix but not only numbers.

So can I format the Part number column to be text even though it will have numbers and then it will work?
fingers crossed.

Hmm seems not. :-(

not sure if my issue can easily be solved with a variation on what we have already?
Alternatively I could have a hidden column next to the part number column called and when i add anew part number in the normal way it could copy the part number to the adjacent cell and an a prefix of say X.
The script could be changed also to add a prefix of X to anything typed into the input box and search the adjacent cell?
May be long way around but is this possible?

tried, changing the single occurrence of match to find but get error
line 13 char 5 typpe mismatch "objxl.iserror"

Gone back to earlier code for find but back to part number not found.

Dim obXL
Dim objWB
Dim rngFnd
dim PartNo

set objXl = CreateObject("Excel.Application")
set objWB = objXl.Workbooks.Open("C:\Users\russell.burke\Desktop\project tracking1.xlsm")

PartNo = InputBox("Enter part number")
set rngFnd = objWB.Sheets("ISIR").Columns(2).Find(PartNo)

If Not rngFnd Is Nothing Then
objXL.Goto objWB.Sheets("ISIR").Cells(rngFnd.Row, 18)
Else
MsgBox "Part number not found"
End If

objXL.Visible = true

Ok will try in Excel and see how it goes. Thanks for all your help.

Hi Norrie, I am not having much success with this one. I am now thinking it may be wiser to simply have a shortcut on the users desktop specific to the sheet they require. This way I can amend it so that engineers go direct to the engineering tab, quality go direct to ISIR etc. I am then thinking of macros (despite wanting to steer away from these originally) which will be specific to each sheet.
So for the shortcut we have been working on I would have a macro button (probably one of many) and this would request part number, issue level and date ISIR passed. This would then add the date to the relevant cell.
When i can get this working I would like to adapt it so that another input box or similar would appear along the lines or "enter another part number" or "exit". This making it more likely that the user will quit the sheet and not leave it open thus stopping other accessing it. I would like to have the macro button in a cell on the sheet rather than on the tool bar.
I dont have a clue how to adapt only of the previous effort so will look to start from scratch.

yes, now i have looked a little closer I think I can manage that :-)
I will make a copy of the live spreadsheet, copy to my desktop and bring in your macro for starters.

thanks for your patience.

taken the code that you supplied in the example work book i uploaded.
brought it into the new spreadsheet and working as before, that is to say only picking up cells with text and returning a not found.
I have converted it to a macro also with the same result.

Sub ISIRPartnumber()

Dim Res As Variant
Dim PartNo As String

PartNo = InputBox("Enter part number")

Res = Application.Match(PartNo, Sheets("ISIR").Columns(2), 0)

If Not IsError(Res) Then
Application.Goto Sheets("ISIR").Cells(Res, 18)
Else
MsgBox "Part number not found"
End If
End Sub

Any idea's?

Hi Jim, this works but only if the input partnumber is alphanumeric. The part numbers can be numeric, alpha or a mix. If i enter a numeric part number in to the inputbox then i get the error 'run time error 13: type mismatch. debug hi-lights the line
Res = Application.Match(PartNo, Sheets("ISIR").Columns(2), 0)

Also as I have partnumbers which are the same but have different issue numbers I would need the input box to request part number, issue and then date.

Maybe?

Dim PartNo As Variant

Hi John, I already tried that and and get a run time error 13 type mismatch. If i change Res to a variant or string I dont get the error but also the part number is not found when its a number only.

I have the following suggestion which will work with both but I am unable to adapt to my requirement.
I need it to request part number, then issue and then date. It will then go to the date cell and enter the date from the date inputbox based on the partnumber and issue I enter.
The issue is in next column to partnumber. I need this as i can have a partnumber duplicated but it would have a different issue.

Hope this makes sense?

Sub M_snb()
On Error Resume Next

Application.Goto Sheets("ISIR").Columns(2).Find(Format(InputBox("Enter part number"))).Offset(, 17)
If Err.Number <> 0 Then MsgBox "Part number not found"
End Sub

Any further advice on this ones guys as looking like I am facing defeat. I could live with finding the correct cell for data entry (date) by entering part number and issue only. But must be able to locate the exact part number with matching issue reference.

Thanks for all help so far