Hej igen Sleipner!

För det första förstår jag inte riktigt syftet med koden. Det enda koden gör är att radera alla namngivna områden och dropdown-listor, för att sen återskapa dem igen. Detta sker varenda gång man aktiverar arbetsbladet "Fundfakt-kateg-vinkl" - och med exakt samma parametrar. Vad är syftet med det...?

För det andra så räcker det inte med att ändra cellreferenserna till A8:A264 eftersom fältet "Källa" i dataverifieringsfönstret stödjer max 256 tecken, inte rader. I dessa 256 tecken räknas också separationstecknena semikolon (;) in. Så i teorin kan man lista max 128 enteckens valmöjligheter (a;b;c;d;e;f;1;2;3; etc.) i det fältet.

De nuvarande strängarna är;

• "Angle" (*Chang: zapato*;cc-bb;cc-bc;cc-cb;*Chang: coche*;Am-Va;Va-Am;*Chang: bocca*;St-Öp;Öp-St;*Chang: misc*) 101 tecken lång
• "Angle1" (*Test Categories1*;categ1;categ2;categ3;categ4;categ5;*Test Categories2*;cat1;cat2;cat3;cat4;cat5;cat6) 102 tecken
• "Angle2" (*Test Fundamentals1*;Fundam1;Fundam2;Fundam3;Fundam4;Fundam5;*Test Fundamentals2*;Fund1;Fund2;Fund3) 99 tecken lång

Det absolut enklaste vore om dataverifieringskällan kunde pekas om till det namngivna områdena Angle, Angle1 och Angle2 istället. Då skulle du helt slippa problemet med antalet tecken, men å andra sidan kommer alla tomma/blanka celler med i dropdown-listan då. Detta kan också lösas, dock med mera arbete och baserat på Excelversionen ni använder.

Lite beroende på vilken version av Excel du/ni använder - och Excelfunktionerna måste vara bakåtkompatibel med - så kan man lösa det på lite olika sätt. Vilken version av Excel är det här Exceldokumnetet och koden skriven för att vara kompatibel med?

Så sammanfattningsvis har jag två frågor till dig.

1. Vad är syftet med koden?
2. Vilken version av Excel ska detta Exceldokument och VBA-kod vara kompatibel med?

Tack på förhand!

Hej igen!

Tack för mycket utförligt svar Sleipner!

Då är utgångsparametrarna definierade - perfekt!

Eftersom lösningen måste vara kompatibel med Excel 2019 (och äldre) - där vi inte har tillgång till dynamiska matrisformler - så kommer vi behöva implementera en del större justeringar.

1. För att få till ett dynamiskt namgivet område (Dynamic Named Range) som kan eliminera alla blanka/tomma celler i Excel 2019 och äldre behöver vi använda oss av en hjälpkolumn/tabell där vi har filtrerat källdatan som dropdown-listorna ska peka på. I nuläget görs detta i VBA och sparas i en matrisvariabel (arrSource) i minnet vilken sedan skrivs ut till fältet "Källa" i dataverifieringsfältet. Denna metod är som jag tidigare beskrivit begränsad till 255 (inte 256) tecken och kan därför inte användas.
2. Det finns ingen anledning att både radera och återskapa de namngivna områdena och dropdown-listorna vid varje aktivering av arket "Fundfakt-kateg-vinkl" om vi har ett dynamiskt namngivet område. Det dynamiska området kommer per automatik uppdatera dropdown-listornas valbara alternativ utan att någon kod behöver köras.

Jag behöver veta var i dokumentet du vill spara de tre nya dynamiska namngivna områdena (listorna) som måste skapas med hjälp av antingen manuella Excelformler direkt i kalkylbladen eller med hjälp av VBA-kod som skapar formlerna eller listorna. Kom ihåg att om de dynamiska dropdown-listorna är det enda du behöver VBA för att åstadkomma så kan vi helt eliminera behovet av VBA med dynamiska namngivna områden som skrivs enbart med hjälp av Excelformler.

1. På ett eget/nytt kalkylblad?
2. På samma blad som källdatan återfinns, dvs. "Angles for estimation", "Categories for estimation" samt "Fundamentals for estimation"?
3. På arbetsbladet "Overview"?

Detta ska jag kunna hjälpa dig med relativt snabbt så fort jag fått svar på ovan.

Hej igen Sleipner,

Om kravet är att filtrera bort tomma celler måste man tyvärr använda en hjälpkolumn oavsett vilken Excelversion man använder eftersom man inte kan använda en matrisformel i fältet "Källa:". Enbart ett kontinuerligt cellområde (lista) med värden godtas som input till fältet "Källa:" i dataverifiering. 

Lösning

Jag har raderat VBA-modulen DropDownOP samt all kod i arbetsbladet "Fundfakt-kateg-vinkl" förutom nedan kod så du får behålla funktionen som inte tillåter användarna att välja en rubrikrad i dropdownlistan.

Det jag ändrat från originalkoden är gulmarkerat nedan

Sub Worksheet_Change(ByVal Target As Range)

 'limit the event to trigger ONLY THE NECESSARY DROPDOWN RANGES:

 If Not Intersect(Target, Union(Me.Range("AC4:AI18"), Me.Range("Q4:W18"), Me.Range("A4:G18"))) Is Nothing Then

    If Target.Cells.Count > 1 Then Exit Sub

    If Target.Value = "" Then Exit Sub

    If Left(Target.Value, 1) = "*" And Right(Target.Value, 1) = "*" Then

        MsgBox "You can't select a heading: '" & Target.Value + "'" & vbCr & vbCr & vbTab & "Try again. ", vbCritical

        Application.EnableEvents = False

        'Target.Value = ""

         Application.Undo  'place back the value existing before change!

        Application.EnableEvents = True

    End If

  End If

End Sub

Steg 1

I brist på tillgång till den dynamiska matrisfunktionen FILTER() i Excel 2019 och äldre måste vi skapa vår egen funktion för att flitrera bort tomma celler.

Markera cell B8 på exempelvis fliken "Angles for estimation".

(Observera! Det är mycket viktigt att ha cell B8 markerad för formeln innhåller relativa cellreferenser.)

Starta namnhanteraren med CTRL+F3. Skapa funktionen "FxFilter" enligt nedan formel.

=OMFEL(INDEX(!$A$8:$A$1008;MINSTA(OM(ÄRTEXT(!$A$8:$A$1008);RAD(!$A$1:$A$1001));RAD(!A1)));"")

Spara och stäng Namnhanteraren.

Åter på fliken "Angles for estimation" skriv in formel vi skapade i cell B8.

=FxFilter

Tryck CTRL+Shift+Enter för att skapa en matrisformel.

Matrisformeln kommer i formelfältet omges med "Curly Brackets" { och } om du gjort rätt.

{=FxFilter}

Kopiera sedan ner formeln ända till cell B1008. Om du har fått till det rätt så kommer det se ut så här i kolumn B.

Gör samma sak i cell B8 och nedåt på fliken "Categories for estimation".

{=FxFilter}

Samt i cell B8 och nedåt på fliken "Fundamentals for estimation".

{=FxFilter}

Steg 2

Starta återigen namnhanteraren med CTRL+F3. Skapa följande tre namngivna formler;

AnglesDropDownOptions

='Angles for estimation'!$B$8:INDEX('Angles for estimation'!$B$8:$B$1008;PRODUKTSUMMA(--('Angles for estimation'!$B$8:$B$1008<>"")))

CategoriesDropDownOptions

='Categories for estimation'!$B$8:INDEX('Categories for estimation'!$B$8:$B$1008;PRODUKTSUMMA(--('Categories for estimation'!$B$8:$B$1008<>"")))

FundamentalsDropDownOptions

='Fundamentals for estimation'!$B$8:INDEX('Fundamentals for estimation'!$B$8:$B$158;PRODUKTSUMMA(--('Fundamentals for estimation'!$B$8:$B$158<>"")))

Så här ska det se ut när det är klart.

Steg 3

Gå till fliken "Fundfakt-kateg-vinkl" och markera cellområde AC4:AI18.

Välj sedan "Dataverifiering" från menyalternativet "DATA". Välj "Lista" under sektionen "Tillåt" och sen =AnglesDropDownOptions i fältet "Källa:".

Så här ska det se ut.

Under flikarna "Indatameddelande" samt "Felmeddelande" kan du lägga in vad du vill.

Gör samma sak för de övriga två cellområdena på fliken "Fundfakt-kateg-vinkl".

I område Q4:W18 ange Källa: =CategoriesDropDownOptions

Samt i område A4:G18 ange Källa: =FundamentalsDropDownOptions

Nu kan du testköra ditt nya - nästan VBA-fria - Excelark och sen gömma kolumn B på källdataflikarna när allt fungerar som det ska.

Lycka till!

Om jag skulle välja att utelämna funktionen med att inte kunna välja rubriker och alltså ta bort VBA-kod helt från arbetsbladet Fundfakt-kateg-vinkl, det är väl bara att ta bort hela koden va? Tanken jag har är att lämna plats för VBA-kod för viktigare funktioner som rör beräkningar av viktning och sannolikhet.

Hej Gondi,

Då behåller jag din kod i arbetsbladet ändå. 

Jag började i morse med att implementera din lösning i min master-Excel men det blir krångel på grund av att jag har en massa tidigare versioner där vissa av versionerna är korrupta. Jag har koll iallafall på exakt status för varje version, men det tar tid, måste gå försiktigt fram. Det jag gör är bland annat att kopiera och klistra in de fyra arbetsbladen Fundfakt-kateg-vinkl, Fundamentals for estimation, Categories for estimation och Angles for estimation från en senare version till en "säker" version som inte är korrupt samt infoga fler kolumner på ett femte arbetsblad. De låter okomplicerat, men jag fick samma felmeddelande som tidigare när jag försöker öppna arbetsboken igen. Inte efter utbyte av arbetsbladen men efter att jag lagt till en massa kolumner på ett femte arbetsblad, vilket inte ska orsaka något problem förstås. Så det verkar som denna version jag försökte med var korrupt. 

Är det säkraste att ta det i denna ordningsföljd?:

1. Öppna en version av arbetsboken som inte ger några felmeddelanden så att inte arbetsboken från början är korrumperad. Använd denna för vidareutveckling efter att jag döpt om arbetsboken.

2. Ta bort tidigare VBA-koder, särskilt modulkoden DropDownOP. 

3. Klistra in Gondis nya arbetsbladskod på arbetsbladet Fundfakt-kateg-vinkl och ändra VBA-koden för arbetsbladet Fundfakt-kateg-vinkl för ranges 14x3 genom att lägga till fler ranges i koden. Dubbelkolla att ranges är korrekta.

4. Byt ut arbetsbladen Angles for estimation, Categories for estimation, Fundamentals for estimation och Fundfakt-kateg-vinkl.

5. Skjut in en B-kolumn för Gondis formler på vart och ett av listrutornas tre källarbetsblad.

6. På ett nytt, femte arbetsblad, ínfoga nya kolumner och skriv dit rubrikerna för kolumnerna A:O.

7. Implementera Gondis lösning på kalkylbladet Fundfakt-kateg-vinkl och på de tre källarbetsbladen.