VB script in Excel using Forms

[spennyd]

Ok, it's been a long time since my college classes on vb scripting, so I'm hoping for a point in the right direction.

In an Excel spreadsheet, on one tab, I have a list of about 200 servers and how they appear on racks in our datacenter. It's a rough visual map of the center. On another tab I have all the information regarding each server.

Is there a way to click on a server name, or a button next to it if that's what I have to use, and in so doing have a text box populate with information from the second tab regarding that server? I know how to do a simple vlookup to find that information, but how do I script it so that by clicking in the cell, or a button, it populates a text box with that info?

Sorry if this is a bit confusing, or even if it's a bit too much for someone as rusty as myself. But any help is MUCH MUCH MUCH appreciated.

[RobDog888]

Welcome to the Forums.

Two options,

1.) Create hyperlinks on the server names to link to the second sheet.

2.) Use VBA to populate a textbox on your sheet1 with the specs from sheet2.

I think #1 is easier.

'Behind Sheet1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Only act on cells in the first column "A"
    If ActiveCell.Column = 1 Then
        If ActiveCell.Hyperlinks.Count = 0 Then
            ActiveCell.Hyperlinks.Add ActiveCell, Sheet2.Cells(ActiveCell.Row, 1)
        End If
        'ActiveCell.Hyperlinks.Item(1).Follow False 'Isnt working on my system?
        Sheet2.Activate
        Sheet2.Cells(ActiveCell.Row, 1).Activate
    End If
End Sub

[spennyd]

RobDog, you rock! Your code didn't work perfectly (never does, does it?), but I think it got me going in the right direction. Thanks man!

[RobDog888]

Thanks! You know we all have an "off-day" once in a while.

If this ends up not working out good enough, just drop a thread in here and I will try to help out more.