VBA question

asiralia

New Member
Joined
Aug 1, 2007
Messages
2
I feel a little rude asking for help again...


Dave helped and his suggestion works beautifully!!!

I have a summary Tab which has a column with a value WS1, WS2, WS3 in column A which tells the user on which worksheet they can find this row on.

In column C there is an ID Variable. Is there a way to code so the user would press a button and take the user to the spreadsheet and to the row that the ID variable finds its row on the WS1 or WS2 or WS3?

Thanks for any help and suggestions..
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
I think this code will work. It is intended to run when the cursor is in the cell where the ID is (in column C, with sheet names in column A).

Code:
Sub MoveToIDSelected()
Dim xRay, shtName
Dim rngFound As Range
Dim MasterSheet As Worksheet
    
Set MasterSheet = ActiveSheet

If Selection.Count > 1 Then End

'Get values and switch sheets
xRay = Selection.Value
shtName = Selection.Offset(0, -2).Value
On Error GoTo HandlerSheetError
Worksheets(shtName).Activate

'Find ID
On Error Resume Next
With ActiveSheet
    Set rngFound = .Cells.Find( _
        What:=xRay, _
        LookIn:=xlValues, _
        LookAt:=xlWhole)
    
    If rngFound Is Nothing Then
        MsgBox "ID not found."
        MasterSheet.Activate
    Else
        rngFound.Select
    End If
End With

Exit Sub
HandlerSheetError:
MsgBox ("An error occurred. The sheet could not be activated")
End Sub

How do you want it activated? It might be easiest to have a keyboard shortcut. Other ideas would be a command button, a custom menu, or a right-click menu for the cell. Sorry, but toolbar buttons I don't know yet.

Regards
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Question for Excel gurus out there:

a double-click event would be awesome here but there is an issue with activating the other sheets ... the cell with the cursor in it (now in edit mode) needs to be unselected first. Can this be done?

Regards
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
Asiralia:
The code I gave you before can go on the main sheet with the list of sheet names and ID's, or in a standard module.

On the Sheet also put:
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column = 3 Then
        Cancel = True
        Call MoveToIDSelected
    End If
End Sub

this will call (run) the routine whenever an ID is double-clicked.

Thanks Jindon.

Regards.
 

Forum statistics

Threads
1,181,647
Messages
5,931,210
Members
436,784
Latest member
amuljono

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
Top