Display Title Row Heading based on Active cell

[Jak]

Hi All

I am looking for a bit of VB code to add to a project. The purpose is to display the Title Row Heading based on the Active Cell in a text box on my userform.

Example:

On a spreadsheet, Range ("F1:AS1") has the following titles; Q1, Q2, Q3 etc up to Q40. If the user selects cell F2 then Q1 would be displayed in the textbox. If J19 is selected the Q5 would be displayed. If a cell outside the range ("F:AS") is selected then the textbox should be blank.

Any help with some VB would be greatly appreciated.

Thanks

 

[Andrew Poulsom]

How about?

Private Sub UserForm_Initialize()
    With ActiveCell
        If .Column < 6 Or .Column > 45 Then
            TextBox1.Text = ""
        Else
            TextBox1.Text = .EntireColumn.Cells(1, 1).Value
        End If
    End With
End Sub

 

[ravishankar]

Hi
paste the following codes in the macro window ( alt F11)

a = activecell.row
b = activecell.column
textbox1.text = cells(1,b-5).value ' or "Q" & b-5

Run the macro
ravi

 

[Jak]

Hi Andrew and ravishankar

Thanks for the quick replies, problem sorted.