combine 2 VBA codes

[Wim Geuens]

hi,

can someone help me to combine the 2 codes underneath.

i want it to do the following:
inputbox that allows me to select a cell
automatically opens the workbook and jumps tot the cell that contains that nummer
If I press ok in the inputbox without selecting a cell, it just needs to open the file
When I press cancel, it should do nothing

thanks in advance

W

Sub test()
 
    Dim wkbOpen As Workbook
    Dim wks As Worksheet
    Dim Cell As Range
    Dim FoundCell As Range
    Dim Wkb As Variant
 
    On Error Resume Next
    Set Cell = Application.InputBox( _
        Prompt:="Please a cell containing the desired value.", _
        Title:="Select a Cell", _
        Type:=8)
    On Error GoTo 0
   
    If Cell Is Nothing Then Exit Sub
   
    Wkb = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", FilterIndex:=1, Title:="Select a Workbook", MultiSelect:=False)
       
    If Wkb = False Then Exit Sub
   
    Application.ScreenUpdating = False
   
    Set wkbOpen = Workbooks.Open(Filename:=Wkb)
       
    With wkbOpen
        For Each wks In wkbOpen.Worksheets
            Set FoundCell = wks.Cells.Find(what:=Cell.Value, _
                LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False) 'Adjust the parameters, accordingly
            If Not FoundCell Is Nothing Then
                Cell.Offset(, 1).Value = FoundCell.Row
                .Close savechanges:=False
                Exit Sub
            End If
        Next wks
        .Close savechanges:=False
    End With
   
    Application.ScreenUpdating = True
   
    MsgBox "Lookup value not found..."
   
End Sub

and

Sub Button Click()
 
 
Dim Findstring As Variant
    Dim Rng As Range
    Findstring = InputBox("Vul hier de nummer in")
         
    If Findstring = blank Then
    Workbooks.Open Filename:="R:\test.xls", ReadOnly:=True
         
    End If
   
    If Trim(Findstring) <> "" Then
        Workbooks.Open Filename:="R:\test.xls", ReadOnly:=True
        With Sheets("Details").Range("A:M")
            Set Rng = .Find(what:=Findstring, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            Lookat:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "nummer niet gevonden"
            End If
        End With
    End If
End Sub

 

[NARAYANK991]

Hi Wim ,

There is a problem with this requirement :

If I press ok in the inputbox without selecting a cell, it just needs to open the file

When you use Type:=8 with the Inputbox method , you cannot press OK without selecting a cell ; you can press Cancel , in which case the return value will be Nothing.

Narayan

 

[Wim Geuens]

is there anyway that i can get it to work like i want it?
or is that impossile?

 

[NARAYANK991]

Hi Wim ,

The problem is using Type:=8 in the Inputbox method ; if you use any other number , then you cannot select a worksheet cell ; you will have to enter a worksheet cell address e.g. C7 or J18 ; the problem with this is that you have to validate any text which is entered so that the code does not try to locate a cell such as ABCD1234.

You can have requirements such as the following :

1. Open the workbook whose name is hard-coded within the VBA procedure test

2. Display the Inputbox and allow the user to :

a. Select a cell and press OK
b. Press Cancel

3. If the user presses OK , and the cell contains a valid value , then the code jumps to the location in the opened workbook which contains that value ; if the cell does not contain a valid value , or the value is not found in the opened workbook , then the code displays the appropriate message and exits , doing nothing.

4. If the user presses Cancel , the code does nothing , and the opened workbook remains open.

Narayan

 

[Wim Geuens]

thanks, that works