Hello Mike
What are the matching restrictions?
(think about LCASE an UCASE and Application.Proper functions)
I would also suggest that you use the Application.Inputbox method (instead
of the Inputbox function) which will allow for better control of the users'
input.
In your case, you are expecting text input then:
xcountry = Application.InputBox(iprompt1, ititle1, , , , , , 2)
xinst = Application.InputBox(iprompt2, ititle2, , , , , , 2)

Apart from these little suggestions, you could use an intermediate function
to check for the existing worksheet:
Function SheetExists(strShName As String) As Boolean
On Error Resume Next
SheetExists = Sheets(strShName).Name < ""
Err.Clear
On Error Goto 0
End Function

HTH
Cordially
Pascal


"mike" a écrit dans le message de news:
...
Hi all,

I have the following macro that allows the user to input a country name
(sheet name) and company name to search and produce a report or msgbox.
The problem is that the macro is case sensitive...
ie. it will only work if the user enters exactly the names exactly as they
appear.

Therefore is is possible to edit the macro to search for the inputs
regardless of case and maybe for similar spelling?

Thanks in advance..

mike

Code follows:

Sub instreport()


' state the dimensions and variables
Dim oldsheet As String
Dim i As Integer
Dim SheetName As String
'r2 and r6 are public variables

Dim xcountry As String 'the country you wish to search
Dim xinst As String 'the institution type you wish to search for
Dim today 'today's date to be included in the report

Dim Newsh As Worksheet
Dim Basebook As Workbook



'if the user clicks cancels exit the macro
macrostart = MsgBox(startprompt, startbutton, starttitle)
If macrostart = vbCancel Then
Exit Sub
Else


'inputbox for country
iprompt1 = "Please enter the name of the country to search." & vbNewLine &
vbNewLine & "Please note that your entry must match the available country
tabs and is CASE sensitive."
ititle1 = filename + "\Report Creation Tool"
xcountry = InputBox(iprompt1, ititle1)

'inputbox for institution type - currently only three options available
iprompt2 = "Please enter the institution type to search." & vbNewLine &
vbNewLine & "Current categories a" & vbNewLine & "Bank" & vbNewLine &
"Broker" & vbNewLine & "Other" & vbNewLine & "Please note that entries are
CASE sensitive."
ititle2 = filename + "\Report Creation Tool"
xinst = InputBox(iprompt2, ititle2)

'confirm that selection is correct and continue
mPrompt1 = "Please confirm that you wish to create the following report...
"
& vbNewLine & "Institution type: " + xinst & vbNewLine & "Country: " +
xcountry & vbNewLine & vbNewLine & "Your report will be created and placed
before the Front Page."
mbutton1 = vbYesNo + vbQuestion
mTitle1 = filename + "\Report Creation Tool"
repconf = MsgBox(mPrompt1, mbutton1, mTitle1) 'confirm details before
writing report.


If repconf = vbYes Then 'if the user clicks yes, the macro continues


SheetName = xinst + " Report, " + xcountry 'name of the new sheet based on
input

On Error GoTo CreateNewSheet
Sheets(SheetName).Activate
xsubprompt = "The report you have requested already exists." &
vbNewLine
& "The active sheet is now: " & vbNewLine & SheetName
xsubbutton = vbOKOnly + vbExclamation
xsubtitle = filename + "\Report Creation Tool"
xsub = MsgBox(xsubprompt, xsubbutton, xsubtitle)
Exit Sub
CreateNewSheet:
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = SheetName

ThisWorkbook.Sheets(SheetName).Tab.ColorIndex = 45

Sheets(SheetName).Activate
Range("A1").Value = Date
Range("A2").Value = SheetName
Range("A4").Value = "Company Name"
Range("B4").Value = "Function1"

Call formatreport

Application.ScreenUpdating = False

i = 0
Sheets(xcountry).Activate
Range("d3").Select
If ActiveCell < "" Then
Do Until ActiveCell = ""
If ActiveCell = xinst Then
c2 = ActiveCell.Offset(0, -2)
c5 = ActiveCell.Offset(0, 1)
c6 = ActiveCell.Offset(0, 2)
c10 = ActiveCell.Offset(0, 6)
c12 = ActiveCell.Offset(0, 8)
c13 = ActiveCell.Offset(0, 9)
c16 = ActiveCell.Offset(0, 12)
c17 = ActiveCell.Offset(0, 13)
c23 = ActiveCell.Offset(0, 19)
c21 = ActiveCell.Offset(0, 18)
c24 = ActiveCell.Offset(0, 20)
c26 = ActiveCell.Offset(0, 22)
c27 = ActiveCell.Offset(0, 23)
c30 = ActiveCell.Offset(0, 26)

i = i + 1
Call PasteMeHere(xcountry, i, SheetName)
End If
ActiveCell.Offset(1, 0).Select
Loop

Sheets(SheetName).Activate
Columns("A:N").Select
Selection.Columns.AutoFit

finishtool = MsgBox(endprompt, endbutton, endtitle)


End If
Else: Exit Sub
End If
End If


Application.ScreenUpdating = True
End Sub