Excel VBA: DropDown Select Macro

[Nitya0808]

Hi All,

I have an excel, where a drop down is created using data validation icon in Excel. there are 8 entries.

There are 8 other hidden sheets in the excel whose name match that of the entries in the drop down list.

I need a piece of code, where when the selection is made in the drop down list: Cell C5, then the data from the particular sheet is displayed as a pop up.

Ex: I have 5 sheets named A,B,C,D,E.

When B is selected from the drop down list in the master sheet, then the contents of Sheet B (A1 to D24), is displayed as a pop up.

Thanks
Nitya

 

[gallen]

Re: Excel VBA: DropDown Selct Macro Help

As a pop up you could only loop through each cell, get the content and add it to a string value then display that string value, however you don't say how you would like the contents displayed. This loops through each cell and then puts the values in a msgBox:

Private Sub Worksheet_Change(ByVal Target As Range)

'Assuming the string value in the dropdown is exactly the same name as the sheet name:


Dim sName As String
Dim s As String
Dim r As Range
Dim c As Range


    If Target.Address = "$C$5" Then 'User has changed the drop down
        sName = Target.Value
        Set r = Sheets(sName).Range("A1:D24")
        For Each r In r.Cells
            
            s = s & vbNewLine & r.Value
        Next
    End If
    MsgBox s
End Sub

It needs tidying but let me know if it's something like what you need

 

[Nitya0808]

Re: Excel VBA: DropDown Selct Macro Help

Hi When i tried this it gives me a error Expected End Sub

Sub Display()
'
' Display Macro
'
Private Sub Worksheet_Change(ByVal Target As Range)


'Assuming the string value in the dropdown is exactly the same name as the sheet name:




Dim sName As String
Dim s As String
Dim r As Range
Dim c As Range




If Target.Address = "$C$5" Then 'User has changed the drop down
sName = Target.Value
Set r = Sheets(sName).Range("A1:D24")
For Each r In r.Cells

s = s & vbNewLine & r.Value
Next
End If
MsgBox s
End Sub


Application.Goto Reference:="Display"
ActiveWorkbook.Save
ActiveWorkbook.Save
End Sub

 

[Nitya0808]

Re: Excel VBA: DropDown Selct Macro Help

Hi,

Thanks... But i am getting an error called Expecting End Sub.

Also, the pop up could jus be as a image of all the data. If thats difficult it can even come up in the same sheet from lets say G5.

 

[gallen]

Re: Excel VBA: DropDown Selct Macro Help

Application.Goto Reference:="Display"
ActiveWorkbook.Save
ActiveWorkbook.Save
End Sub

Delete all that. Don't know where that's come from.

 

[bhos123]

Re: Excel VBA: DropDown Selct Macro Help

Sub Display()
'
' Display Macro
'


also delete this.

 

[Nitya0808]

Re: Excel VBA: DropDown Selct Macro Help

Application.Goto Reference:="Display"
ActiveWorkbook.Save
ActiveWorkbook.Save
End Sub

Delete all that. Don't know where that's come from.

Thanks it worked... but the table from the oter sheets dont come nicely. We would need some formatting.. It needs to come up as a table itself.

Can that be done.

If thats not possible, we can put it on the sheet itself from cell G5

 

[Nitya0808]

Re: Excel VBA: DropDown Selct Macro Help

Is this possible???? Please help....

 

[gallen]

Re: Excel VBA: DropDown Selct Macro Help

It's possible to copy the range as a picture then display in pop-up form but Why do this when you can just display the sheet with the cells?

 

[Nitya0808]

Re: Excel VBA: DropDown Selct Macro Help

It's possible to copy the range as a picture then display in pop-up form but Why do this when you can just display the sheet with the cells?

Currently i am able to display it on the sheet itself. Jus was thinking if that would be better looking.