ListObjecs en Combobox Vraag

[Excelvbas01]

Hallo Allen,
Ik heb een stukje code.

With Sheets("Blad1").ListObjects(1)

deze werkt goed. Maar als ik ("Blad1") naar (CoboBox1.Text) wil wijzigen, dan geeft deze foutmelding.
Weet iemand hoe ik dit kan wijzigen?

Alvast bedankt.

De hele code:

Private sub UserForm_Activate ()
With Sheets("Blad1").ListObjects(1)
   Ar = .DataBodyRange
     CoboBox1.list = Application.Transpose(.HeadRowRange.Value)
     Zoek.Value = Application.Max(.Range.Columns(1)+1
ListBox1.list = Ar
Zoek = ""
ListBox1.Clear
End With
End Sub

 

[edmoor]

Uiteraard gaat dat niet goed.
Een combobox is een heel ander object.

 

[Excelvbas01]

bedankt voor snelle reactie.

Ok, is dit op te lossen?

 

[edmoor]

Vast wel.
Als je een voorbeeld document plaatst en daarbij uitlegt wat precies je bedoeling is.

 

[Excelvbas01]

Zie bestand.
De bedoeling is zoeken in de Exceblad welke aangegeven in CombBox1. De ene wil ik zoeken in blad “bld” en ander keer in blad “Lijst”.

Om dit voor elkaar te krijgen moet ik dus Sheets("bld") vervangen door sheets(ComboBox1.Text).

 

[VenA]

Waarom zou je dat willen? Het lekker moeilijk maken of heeft het een betere reden?

 

[emields]

God weet, en gij waarschijnlijk ook, wat je wil bewijzen. Maar is dit wat je wil bewerkstelligen.

 

[Excelvbas01]

Bedankt allemaal voor het meedenken.
ik wil het zeker niet moeilijk maken.
Het enige wat ik wil is dat ik in verschillende sheets kan zoeken. Niet tegelijk maar afzonderlijk.
Om dit te realiseren heb ik een combobox met daarin Headerrij(No, Naam, adres enz.). De code hierboven werk wel. Het probleem van deze code is dat ik maar in een sheet kan zoeken. Als ik in ander sheet wil zoek dan moet ik naar de code gaan en daar de naam van de sheet veranderen en dat is niet te doen.
Als er andere Ideeën zijn, graag.

Ik heb geprobeerd 2 codes te combineren ook dat lukt.

With sheets("bld").ListObjects(1)
Ar = .DataBodyRange
     Combo.List = Application.Transpose(.HeaderRowRange.Value)
     Zoek.Value = Application.Max(.Range.Columns(1)) + 1
ListBox1.List = Ar
Zoek = ""
ListBox1.Clear
End With

With sheets("Lijs").ListObjects(1)
Ar = .DataBodyRange
     Combo.List = Application.Transpose(.HeaderRowRange.Value)
     Zoek.Value = Application.Max(.Range.Columns(1)) + 1
ListBox1.List = Ar
Zoek = ""
ListBox1.Clear
End With

/CODE]

 

[emields]

Stel uw gegevens dan op 1 blad.

 

[jkpieterse]

De juiste syntax is

With sheets(ListBox1.Value).ListObjects(1)

maar dan moet er wel iets in de listbox gekozen zijn, anders krijg je een foutmelding.

 

[VenA]

Om de listbox en combobox uit meerdere sheets te vullen

Private Sub UserForm_Initialize()
  Set d = CreateObject("Scripting.Dictionary")
  Set d1 = CreateObject("Scripting.Dictionary")
  For Each sh In sheets
    Ar = sh.Cells(1).CurrentRegion
    For j = 2 To UBound(Ar)
      d(d.Count + 1) = Application.Index(Ar, j, 0)
      d1(Ar(j, 1)) = ""
    Next j
  Next sh
  ListBox1.List = Application.Index(d.items, 0, 0)
  Combo.List = d1.keys
End Sub

[Edit] nog een paar aanpassingen gedaan zie bestandje.

 

[Excelvbas01]

Het is een ander benadering dan wat ik bedoel.
Het werk en daar gaat het om.
Bedank allen.