• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to search sheet by partial name and message box to go to next search if the right sheet name is not found

Status
Not open for further replies.
J

Jonnathanjons

Member
Hello ,
Can you help to write a script which can search for a sheet name by an input box. and the also a message box to Go to next search if the right value is not found. It should search the name partially and go to next matching sheet name as I hit the message box Search next or Ok to Cancel search .
It should work from personal.xlsb file to execute for an active workbook.
I have a template which can contain more than 50 sheets and currently there is no way to search the sheet other than manual way.

Kindly note , would like to have it in a vba wich can be executed from a personal.xlsb file

Code: 
ShtName = inputbox("Enter the sheet name:")
If InStr(UCase(oSheet.Name), UCase(ShtName)) Then
    Msgbox ("Search Next",vbYesNo)
    If Vbyes Then Search next in the sheet matching the sheet name
    Else " Select the sheet chosen by search"
END If   

End Sub
 
I didn't know of a right click option exist on the arrows at the left of the sheets.Thanks Marc. I can live without a macro for that.
 
Last edited by a moderator:
Marc L quoted Jonnathanjons as saying "there is no way", but I don't know where that was said or why. Something like this should work. Warning: untested code. But it may give you the right idea even if there's an error in it:
Code: 
vsn = InputBox("Enter the first part of the sheet name: ")
If vsn = "" Then Exit Sub
bf = False
For Each ows In ThisWorkbook.Sheets
  If ows.Name Like vsn & "*" Then
    bf = True
    Exit For
    End If
  Next ows
If Not bf then 'no match found
Else 'This is the first sheet that matches
You'd have to put this inside a larger loop that allows the operator to look for another hit if the first hit isn't what he wanted, but you can figure that out.
 
BobBridges said:
Marc L quoted Jonnathanjons as saying "there is no way", but I don't know where that was said or why. Something like this should work. Warning: untested code. But it may give you the right idea even if there's an error in it:
Code: 
vsn = InputBox("Enter the first part of the sheet name: ")
If vsn = "" Then Exit Sub
bf = False
For Each ows In ThisWorkbook.Sheets
  If ows.Name Like vsn & "*" Then
    bf = True
    Exit For
    End If
  Next ows
If Not bf then 'no match found
Else 'This is the first sheet that matches
You'd have to put this inside a larger loop that allows the operator to look for another hit if the first hit isn't what he wanted, but you can figure that out.
Click to expand...
Hi Bob,

thanks much for the response. I tired above script but gettting an Compile error " Wrong number of arguments or invalid property assignment

83747
 
Selection, as opposed to a search macro, still seems like a faster and more trouble-free method.
83761
 
Yeah, well, I did say, Jonnathan, that it's untested code; I meant you to try it out and correct it as necessary.

At the start, you showed us a bit of code that I assumed you'd written yourself, so I treated you like a programmer. Was I mistaken? If you don't know what to do with that error message, maybe we need to back up and start from scratch. Do you know what that error message means and how it should be fixed?
 
Not sure really necessary, anyway a VBA basics demonstration for starters :​
Code: 
Sub Demo1()
    Dim S$, Ws As Worksheet
        S = InputBox(vbLf & vbLf & " Search for :", "Worksheet name"):  If S = "" Then Exit Sub
    For Each Ws In ActiveWorkbook.Worksheets
        If InStr(1, Ws.Name, S, 1) Then _
            If MsgBox(Ws.Name & vbLf & vbLf & "Next ?", 4, "Matching worksheet") = 7 Then Ws.Activate: Set Ws = Nothing: Exit For
    Next
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 
Marc L said:
Not sure really necessary, anyway a VBA basics demonstration for starters :​
Code: 
Sub Demo1()
    Dim S$, Ws As Worksheet
        S = InputBox(vbLf & vbLf & " Search for :", "Worksheet name"):  If S = "" Then Exit Sub
    For Each Ws In ActiveWorkbook.Worksheets
        If InStr(1, Ws.Name, S, 1) Then _
            If MsgBox(Ws.Name & vbLf & vbLf & "Next ?", 4, "Matching worksheet") = 7 Then Ws.Activate: Set Ws = Nothing: Exit For
    Next
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
Click to expand...

I get the same error not sure what am I doing wrong here.. I am running it from personal.xlsb file

83765
 
BobBridges said:
Yeah, well, I did say, Jonnathan, that it's untested code; I meant you to try it out and correct it as necessary.

At the start, you showed us a bit of code that I assumed you'd written yourself, so I treated you like a programmer. Was I mistaken? If you don't know what to do with that error message, maybe we need to back up and start from scratch. Do you know what that error message means and how it should be fixed?
Click to expand...
I am not a programmer but do some basic vba scripts . I hope you can explain why I get that error
 
Marc L said:
As it well works on my side …​
​
On VBE side what are the checked references via the Tools menu ?​
And uncheck any reference marked as 'missing', who knows ?!​
​
Click to expand...
On VBE side what are the checked references via the Tools menu ?
VB for Applications
MS Excel 16.0 Object Lib
OLE Automation
MSO 16.0 Object Lib
MS Scripting Runtime
 
Jonnathanjons
Sentences, which ends with ? are questions.
You skipped my the 1st question.
... Have You tried to write other basic code eg S = InputBox(2) to verify that You could use it?
Did You press Shift and 2 to get "? Those too should be correct.
 
Jonnathanjons
It's a challenge to give more ideas without clear answers to basic questions.
#1 I even tested to copy and paste that code - and -
it shows same as original. Your copy and paste gave something else. How did You do that?
#2 You skipped to do basic test with InputBox.
Too many times, thats all -phrase means something else.
 
Jonnathanjons said:
Hello Team , Any reason why am I getting this error and how to fix it?
Click to expand...
83782

That code works for me. but I notice that for you, the case of "inputbox" is wrong. The VBE editor should have automatically corrected the case of keyword "inputbox" to display "InputBox". That it did not suggests something unexplained is going on. As an experiment,change your code to use the Application InputBox instead of the VBA InputBox. In other words, replace this line:
Code: 
S = InputBox(vbLf & vbLf & " Search for :", "Worksheet name"): If S = "" Then Exit Sub
with this:
Code: 
S = Application.InputBox(vbLf & vbLf & " Search for :", "Worksheet name"): If S = "False" Then Exit Sub
and report back.
 
rlv01
Do You have any idea ...
if someone copy and paste that code,
how it can be different than original?
Of course, there are sometimes ... interesting (unwanted) features with Excel
... but this is the 1st time that I've read about this ( copied and pasted are different ).
 
vletm said:
rlv01
Do You have any idea ...
if someone copy and paste that code,
how it can be different than original?
Of course, there are sometimes ... interesting (unwanted) features with Excel
... but this is the 1st time that I've read about this ( copied and pasted are different ).
Click to expand...

It's hard to say without being able to directly inspect the spreasheet; but the failure of the IDE to properly capitalize a keyword like that is a red flag. There's nothing wrong with the code, per se. At a guess, and it's only a guess, I think the OP has done something to mess up the ability to reference the standard VBA InputBox function.

For example, if I wrote my own "inputbox" function (using lower case ), say
Code: 
Public Function inputbox(A, B)
   MsgBox "Alternate Inputbox"
End Function

and put it in the same module , that would do it. Which is why I suggested changing to Application.Inputbox as a test.
 
If nothing in Personal.xlsb (dumb code) then it could be a bad add-in involved or Excel is broken / damaged : to see with your IT.​
​
Do no create any other VBA thread while this issue is not solved on your computer as we won't waste time anymore …​
​
 
Status
Not open for further replies.
Back
Top