SOLVED

VBA - Show data selection in pop-up menu

%3CLINGO-SUB%20id%3D%22lingo-sub-1546441%22%20slang%3D%22en-US%22%3EVBA%20-%20Show%20data%20selection%20in%20pop-up%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546441%22%20slang%3D%22en-US%22%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EHi%20Guys%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3Ehave%20a%20%22little%22%20problem%20where%20I%20need%20a%20little%20help.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22%22%3EI%20have%20a%20user%20form%20with%20a%20list%20field%20where%20I%20take%20data%20from%20a%20table%20from%20another%20worksheet.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EMy%20problem%20is%20that%20it%20only%20shows%20the%20labeling%20area%20and%20not%20the%20data.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-result-transliteration-container%20result-transliteration-container%20transliteration-container%22%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%3CU%3EVBA%20Code%20in%20Scheet1%3C%2FU%3E%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%3CP%3EOption%20Explicit%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_BeforeRightClick(ByVal%20Target%20As%20Excel.Range%2C%20Cancel%20As%20Boolean)%3C%2FP%3E%3CP%3EMe.Unprotect%20(%221234%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20If%20Not%20Intersect(Target%2C%20Range(%22G5%3AEZ6%22))%20Is%20Nothing%20Then%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Cancel%20%3D%20True%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ASK_Auswahl.Show%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20End%20If%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Me.Protect%20(%221234%22)%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CU%3EUser%20Form%20in%20sheet%201%3C%2FU%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Daten%20in%20Userform.JPG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207968i0E7C0ECA8A48A6DE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Daten%20in%20Userform.JPG%22%20alt%3D%22Displayed%20data%20in%20UserForm%20(list%20field)%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EDisplayed%20data%20in%20UserForm%20(list%20field)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3ESheet2%20-%20table%20where%20the%20data%20is%20taken%20from%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Daten%20in%20Tabelle.JPG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207969i85A5A93E1494C474%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Daten%20in%20Tabelle.JPG%22%20alt%3D%22Daten%20in%20Tabelle.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EProperties%20window%20in%20lists%20Box1%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Eigenschaftsfenster_Listenbox.JPG%22%20style%3D%22width%3A%20252px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F207970i7A998516FD5373F0%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Eigenschaftsfenster_Listenbox.JPG%22%20alt%3D%22Properties%20window%20in%20lists%20Box1%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EProperties%20window%20in%20lists%20Box1%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EHow%20can%20I%20get%20all%20data%20from%20Sheet2%20to%20be%20displayed%20in%20the%20user%20form%3F%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3EThx%20in%20Advance%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3E%3CSPAN%3ENikolino%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1546441%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546475%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Show%20data%20selection%20in%20pop-up%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546475%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20ListBox%2C%20the%20ColumnCount%20property%20is%20set%20to%201%20and%20that%20means%20only%201%20column%20would%20be%20displayed%20in%20the%20ListBox.%20Therefore%20increase%20the%20ColumnCount%20property%20of%20the%20ListBox%20according%20to%20the%20source%20data%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546509%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Show%20data%20selection%20in%20pop-up%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546509%22%20slang%3D%22en-US%22%3Ehank%20you%20very%20much%20...%20was%20too%20logical%20to%20have%20seen%20it%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20...%20Now%20that%20I%20can%20see%20it%2C%20I%20can%20understand%20what%20I%20missed%20...%20thank%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1546587%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20-%20Show%20data%20selection%20in%20pop-up%20menu%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1546587%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F722750%22%20target%3D%22_blank%22%3E%40Nikolino%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome!%20Glad%20I%20could%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Trusted Contributor
Hi Guys,
have a "little" problem where I need a little help.
I have a user form with a list field where I take data from a table from another worksheet.
My problem is that it only shows the labeling area and not the data.
 
VBA Code in Scheet1

Option Explicit

Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)

Me.Unprotect ("1234")

    If Not Intersect(Target, Range("G5:EZ6")) Is Nothing Then

        Cancel = True

        ASK_Auswahl.Show

    End If

    Me.Protect ("1234")

End Sub

 

User Form in sheet 1

Displayed data in UserForm (list field)Displayed data in UserForm (list field)

 

Sheet2 - table where the data is taken from

Daten in Tabelle.JPG

 

Properties window in lists Box1

Properties window in lists Box1Properties window in lists Box1

 

How can I get all data from Sheet2 to be displayed in the user form?

 

Thx in Advance

 

Nikolino

 

3 Replies
Best Response confirmed by Nikolino (Trusted Contributor)
Solution

@Nikolino 

In your ListBox, the ColumnCount property is set to 1 and that means only 1 column would be displayed in the ListBox. Therefore increase the ColumnCount property of the ListBox according to the source data range.

hank you very much ... was too logical to have seen it ... Now that I can see it, I can understand what I missed ... thank you.

@Nikolino 

You're welcome! Glad I could help.