Excel: rozevírací seznam s předvolenou hodnotou

Ak sa dobre pamätám, zdroj pre rozbaľovací zoznam musel byť v dávnejších verziách Excelu na rovnakom hárku ako rozbaľovací zoznam.

Máš dve možnosti:

1 zdroj premiestni do toho istého hárka (a môžeš ho vhodne ukryť)

2 nadefinuj si zoznam priamo v nástroji overenia údajov, potom nemusíš mať zdroj nikde v podobe pomocnej tabuľky.
Do políčka "Zdroj" rovno napíš: jedna; dvě; tři; čtyři

Síce hovoríš, že Ti to v 2007 pôvodne fungovalo, ale možno si len zabudol napísať, že medzitým si presunul konštanty do iného hárka...(?)
Alebo že by to bola podmienka len do Excelu 2003? Neviem...Skús... Možno pomôže už ten priamy zdroj... Zatiaľ si neviem predstaviť inú príčinu...

No, je to nějaké divné

A dá se nějak přednastavit defaultní hodnota, abych v těch 99% případech nemusel nic vybírat a aby se vyplnila automaticky sama?

A prečo nevyhovuje ten vzorec?

Když vytvořím v buňce Ověření dat, následně tam ten vzorec nemohu vložit (Excel 2007)

Ověření dat:

Divné je to, že mně ten původně vyrobený sešit v 2007 funguje i v 2007 i v 2019 (ověření dat i podmínky, jak jsem to chtěl). Když to ale uložím v 2019, už to nefunguje v 2007. Pokud na to kašlu a opět to (co mi už nefunguje, protože to bylo uloženo v 2019) uložím v 2007, záhadně to pořád funguje v 2019...

Protože potřebuji pracovat na stejných souborech v 2007 i 2019, hledám tedy způsob, jak z toho vybruslit..

Teď jsem to zkusil udělat v 2019, seznam přímo v ověření a v 2007 to funguje. Zkuaím tedy laborovat tímto směrem, seznam na stejném listu...

Tak jo, je to tím...
Zdroj dat pro ověření musí být na stejném listu, pak po uložení v Excelu 2019 je to funkční i v 2007.

Jen nechápu, proč mi to tedy funguje, když ten seznam mám i jinde. Tedy funguje jaksi polovičatě...

Takže pre opätovné automatické nastavenie defaultnej hodnoty... makro... sledujúce zmenu hárka.
Zmaž všetky vzorce a do modulu hárka vlož:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim AktOblA As Range
Set AktOblA = Range("A3:A27")

    If Application.Intersect(Target, AktOblA) Is Nothing Then Exit Sub
    
    If Target = "" Then
        Range("C" & Target.Row) = ""
        Else
        Range("C" & Target.Row) = "jedna"
    End If

End Sub

Pozor, nefunguje pre hromadné zapisovanie, či mazanie.
Platí pre deklarovanú oblasť (podľa príkladu), takže v skutočnom súbore budeš musieť upraviť definovanie oblasti AktOblA

Tohle omezení Excelu 2007 se dá obejít přes Správce názvů, stačí vytvořit definovaný název se "seznamem dat" z druhého listu a tento název použít v ověření dat.

Díky, tohle je nejpohodlnější řešení (pokud bych začínal od nuly a nechtěl mít listy "zaneřáděné" pomocnými věcmi.
Ověřoval jsem si to teď několikerým otevíráním a ukládáním postupně v 2007 a2019.

Složitější to budu mít tam, kde už mám (rok 2020) zaplněn daty, tam budu muset v 2019 provést to pojmenování rozsahu, ale až od prvního prázdného řádku, protože prostým kopírováním vzorců + ověřování dolů bych přepsal hodnoty, které jsou z defaultu změněny. To mi až tak nevadí (že nezačnu od prvního řádku, protože zadaná data jsou vždy dogma a už se následně nemění.

Akorát mi tedy není jasné, proč mi to (zdroj pro ověřování je na jiném listu a není pojmenován) v 2007 roky fungovalo a pořád funguje, proč to funguje i v 2019 - ale když to v 2019 uložím, v 2007 to nefunguje.

Asi vyřešeno.

A ještě jedna zajímavost. V 2007 má soubor 4,2MB. Otevřu jej v 2019 a bez jakýchkoliv změn uložím, má pak 4MB. Otevřu jej v 2007 a beze změny uložím - a má zase 4,2MB.

Nasírá mě ta divná kompatibilita mezi verzemi Excelu. Pochopil bych, kdybych v v 2019 použil funkci, kterou 2007 nezná (IFS ?). Asi se formát xlsx, xlsm atd. postupně mění, možná optimalizuje, ale dřívější verze Excelu si pak s tím neumí poradit.

Bohužel nemohu tak náhle přijít na 2019 kompletně, musím to postupně vše ověřovat. Jasně, udělat si nějakou tabulku, kde sečtu čtyřem kočkám nohy, tam problém nebude, ale ty moje "velko-aplikace" problémem jsou. Za všech okolností musí být funkční - a nemám čas a chuť riskovat, že se něco pokaká, nemám stovky hodin, abych všechno psal od nuly a další stovky hodin, abych tam pak rval data odjinud. Takže dnes u nás pořád vede 2007, v poměru 3:1. V srpnu, pokud vše půjde podle plánu, bych to změnil na 2:2. Více ne, protože záložní počítače jedou na W7/Pro a tam 2019 nechodí.

A ještě k tomu "když" a "ověření dat" společně:
Nejprve se musí zadat "když", až pak do stejné buňky přidat ověření. Opačně to nejde ani v 2019, stejná chybová hláška , jakou jsem uvedl nahoře.

Vyššie máš riešenie defaultu (v prípade, že sa zmení bunka v stĺpci A na neprázdnu) a zároveň výmazu bunky (v prípade, že sa bunka v stĺpci A zmení na prázdnu), aj bez vzorcov... Makrom... Asi si nečítal...
Potom nepotrebuješ žiadnu funkciu IF, ktorá by bola v konflikte s overením dát. Pôvodný obsah, čo máš v stĺpci C, môžeš nechať tak (zmenami v stĺpci A dôjde činnosťou makra k postupnému nahradeniu vzorcov, pričom nedôjde k nijakému neželanému ovplyvneniu) alebo pre poriadok môžeš zrušiť naraz všetky vzorce prelepením stĺpca C svojimi hodnotami...

A ešte k Tvoj,u postu zo 14:58 o tom, že to už máš čiastočne zaplnené dátami:

- Ak už chceš definovať ten vlastný názov: definuješ ho len raz pre oblasť zdroja pre rozbaľovací zoznam a aplikuješ naraz na celú oblasť, kde majú byť rozbaľovacie zoznamy.
- Na vzorce zabudni, prelep celý stĺpec C hodnotami, tým sa zachová natvrdo všetko, čo tam doteraz máš (aj so zmenenými defaultnými hodnotami) a o ostatné sa Ti namiesto tých vzorcov postará to makro. Takže to nemusíš aplikovať až od prvej prázdnej hodnoty...

Díky, za tipy, vyzkouším.

Ono bohužel ta skutečnost je oproti tomu mému příkladu mnohem složitější a komplikovanější. Ale metody jak_na_to už tu mám, tak se do toho postupně pustím. Rok 2021 bude pochopitelně jednodušší, začíná bez dat.

Uviedol som len princíp, na zváženie použiteľnosti pre Tvoje potreby. Len sa mi zdalo, že si sa obával o uchovanie jestvujúcich dát po zmenených defaultoch... alebo ich porušení po zmene definovania zdroja pre rozbaľovacie zoznamy... a zopakoval si nezlúčiteľnosť vzorca s overením údajov...
Tak veľa zdaru!

Ono je to hlavně složitější v tom, že na jednom listu mám třeba 4 sloupce, kde tohle musím řešit

1) vzoreček na default nebo prázdnou hodnotu
2) ověření dat - tedy když default přepíšu hodnotou ze seznamu povolených textů
3) každý takový sloupec pracuje s jinými hodnotami, na sobě nijak nezávislými

Představ si dlouhý řádek. Na tom, zda je ve sloupci A cokoliv, závisí, jak se bude chovat buňka ve sloupcích E, K, R, S. A pro každý sloupec platí úplně odlišné seznamy příslušných hodnot (např. jedna,dvě,tři - 10,20,30 - N,A - Petr,Pavel). Některé mohou být prázdné (tam tedy ta podmínka není), někde musí být nějaká hodnota (přednastaví se default, ručně je to možno měnit).

Nedokážu si představit, jak tohle narvat do kódu listu.

S tím vzorcem a ověřením to bude asi schůdnější. Pro mě určitě :)

Myslím, že ani tak by to nemal byť problém. Rieš, ako považuješ za vhodné a kdyby něco, tak sme tu... Zahulíme, uvidíme...

No, takže myslíš, že pro více oblastí, kde potřebuju vybírat hodnotu či zadávat default či nechat "nic" by to mohlo fungovat (konktétně) takto?
(změnil jsem si rovnou AktOblA a přidal celkem tři oblasti)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Oblast As Range
Set Oblast = Range("D10:D5000")

    If Application.Intersect(Target, Oblast) Is Nothing Then Exit Sub
    
    If Target = "" Then
        Range("D" & Target.Row) = ""
        Else
        Range("D" & Target.Row) = "tuzemsko"
    End If

Dim Rezim As Range
Set Rezim = Range("K10:K5000")

    If Application.Intersect(Target, Rezim) Is Nothing Then Exit Sub
    
    If Target = "" Then
        Range("K" & Target.Row) = ""
        Else
        Range("K" & Target.Row) = "1"
    End If

Dim Insolvence As Range
Set Insolvence = Range("U10:U5000")

    If Application.Intersect(Target, Insolvence) Is Nothing Then Exit Sub
    
    If Target = "" Then
        Range("U" & Target.Row) = ""
        Else
        Range("U" & Target.Row) = "N"
    End If

End Sub

...jak na to koukám (teď to nemám jak vyzkoušet a za chvíli jedu pryč), tak netuším, zda je to takto poskládáno správně. Aby se po první části (tedy u mě nastavení toho "tuzemsko" - tedy default - to na řádku neukončilo (Then Exit Sub) a ve sloupcích K a U to neudělalo nic.

Možná nastavit ty Dim a Set hned nahoře?

A nad rámec tohoto konkrétního problému, jak by to bylo, kdybych na listu už nějaký VBA kód měl? Muselo by se to zkombinovat nějak dohromady nebo jen připlácnout na konec?
Dejme tomu, že to, co tu teď řeším, bych potřeboval přidat na list, kde už mám tento kód:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Chyba
Application.EnableEvents = False
    If (Target.Column <> 6 _
        And Target.Column <> 7 _
        And Target.Column <> 8 _
        And Target.Column <> 12 _
        And Target.Column <> 16 _
        And Target.Column <> 20 _
        And Target.Column <> 24) _
        Or Target.Row < 5 Or Target > 311299 Or Target < 10100 Or Mid(Target, 6, 1) = "," Or Mid(Target, 7, 1) = "," Then
        Application.EnableEvents = True
        Exit Sub
    End If
    If Mid(Target, 5, 1) > 4 Then
        Stoleti = 19
        Else: Stoleti = 20
    End If
    Target = Left(Target, 2) & "." & Mid(Target, 3, 2) & "." & Stoleti & Right(Target, 2)
    
    Range("AG1:AG2").NumberFormat = ";;;"
    Range("AG1") = Target
    Range("AG2").FormulaR1C1 = "=IF(ISERROR(DATEVALUE(R[-1]C)),""Chyba"","""")"
    If Range("AG2") = "Chyba" Then
        Target.Select
        MsgBox "Zadané datum neexistuje!", vbCritical, "Pozor"
    End If
    Application.EnableEvents = True
    Exit Sub

Chyba:
    Application.EnableEvents = True
    Exit Sub
End Sub

To řešit nepotřebuji, to jsem teď jen vyhrabal z jiného souboru, jde mi jen o princip, jak to "spojit", když obě makra dělají něco jiného a mají platit v různých oblastech listu.

To makro je potrebné zapracovať do toho Tvojho, lebo to všetko sa má odohrávať pri tej istej udalosti: zmene v hárku. To moje nemôžeš len takto strojnásobiť, lebo v prípade, že nie je splnená prvá podmienka (test, či sa zmenila hodnota v oblasti stĺpca A), tak sa všetok nasledujúci kód ignoruje a makro končí. Takže ja by som použil Select Case na výber úkonov podľa toho, v ktorom stĺpci sa nachádza Target. A na začiatku by mohol byť rozsah riadkov (predpokladám, že rovnaký pre všetky spracovávané stĺpce) ošetrený práve tak, že v prípade, že Target je mimo tohto rozsahu riadkov, tak makro skončí. A máš tam chybu, nemôžeš testovať či sa udiala zmena v stĺpci D a zároveň dať naplniť hodnotu v tom istom stĺpci, to by sa Ti zacyklilo. To si sa asi pomýlil, veď aj Tvoj vzorec mal podľa obsahu bunky v stĺpci A určiť hodnotu v stĺpci C!
Takže Tvoj prípad by vyzeral takto:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row < 10 Or Target.Row > 5000 Then Exit Sub
    
    Select Case Target.Column
        Case 3
        't.j. v prípade zmeny v stĺpci C nastavuje obsah v stĺpci D:
            If Target = "" Then
                Range("D" & Target.Row) = ""
                Else: Range("D" & Target.Row) = "tuzemsko"
            End If
        Case 10
        't.j. v prípade zmeny v stĺpci J nastavuje obsah v stĺpci K:
            If Target = "" Then
                Range("K" & Target.Row) = ""
                Else: Range("K" & Target.Row) = "1"
            End If
        Case 20
        't.j. v prípade zmeny v stĺpci T nastavuje obsah v stĺpci U:
            If Target = "" Then
                Range("U" & Target.Row) = ""
                Else: Range("U" & Target.Row) = "N"
            End If
        'atď
        Case Else
            Exit Sub
    End Select
    
End Sub

Prikladám Ti ukážkový súbor, kde som kvôli otestovaniu nastavil prípustný rozsah riadkov od 10 do 20 (v kóde v predchádzajúcej odpovedi som to nastavil po Tebou uvedený riadok 5000)
Napĺňanie "defaultami" som nechal podľa Teba v stĺpcoch D, K a U (tam prídu aj príslušné rozbaľovacie zoznamy) a teda ako testované stĺpce som dal vždy stĺpec pred nimi (pričom, samozrejme, to tak vôbec nemusí byť, ale podľa toho si musíš potom opraviť aj jednotlivé prípady Case. Napríklad ak sa má hodnota v stĺpci D naplniť podľa obsahu bunky v stĺpci A, tak namiesto Case 3 budeš musieť mať Case 1).
Case Else je miesto pre úkony, ktoré sa majú vykonať v prípade, že Target je síce v povolenom rozsahu riadkov, ale nie je ani v jednom zo stĺpcov ošetrených jednotlivými Case-mi.
Pozor! Obsah testovaných buniek môže byť napĺňaný len individuálne (nemôžeš vkladať naraz viac hodnôt, či už cez CTRL+ENTER, alebo kopírovaním oblasti a rovnako nemôžeš mazať naraz viac ako jednu bunku). Dá sa síce aj to ošetriť cez Target.Count ale už by kód geometricky narastal...

Aha

No, vždycky se to posuzuje podle toho, zda ve sloupci A je nějaký text. Pokud ano, nastává rozhodování, co bude v těch dalších sloupcích (default nebo vybraná hodnota). Tohle bude IMHO taky špatně, nebo přinejmenším hloupě napsáno, to "case" je tam asi zbytečně, stačí prostě natvrdo nadefinovat "sloupec A":

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row < 10 Or Target.Row > 5000 Then Exit Sub
    
    Select Case Target.Column
        Case 1

            If Target = "" Then
                Range("D" & Target.Row) = ""
                Else: Range("D" & Target.Row) = "tuzemsko"
            End If

            If Target = "" Then
                Range("K" & Target.Row) = ""
                Else: Range("K" & Target.Row) = "1"
            End If

            If Target = "" Then
                Range("U" & Target.Row) = ""
                Else: Range("U" & Target.Row) = "N"
            End If

        Case Else

            Exit Sub
    End Select
    
End Sub

Tak počkať! To je iná situácia! Pokiaľ sa majú v troch rôznych stĺpcoch nastavovať defaulty len na základe obsahu bunky v stĺpci A, tak sa to všetko zjednoduší a Case je samozrejme zbytočné (súbor v prílohe):

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row < 10 Or Target.Row > 20 Or Target.Column <> 1 Then Exit Sub
    
    If Target = "" Then
        Range("D" & Target.Row) = ""
        Range("K" & Target.Row) = ""
        Range("U" & Target.Row) = ""
        Else:
            Range("D" & Target.Row) = "tuzemsko"
            Range("K" & Target.Row) = "1"
            Range("U" & Target.Row) = "N"
    End If
  
End Sub

Klucí a co k tomu přidat rovnou i to ověření dat?

Klidně
Výborný nápad, veď rozbaľovací zoznam tam stačí mať len vtedy, keď to má význam...

Hoši, máte u mě oba virtuální 12° Plzeň. A až někdy pojedete do Vídně, tak se cestou stavte na pohárek..
Díky moc, s tím si už snadno poradím.

Značím vyřešeno a ukládám do archivu.

Akorát mi pořád hlodá v hlavě, proč se to tak divně chovalo v tom příkladě, co jsem uvedl (ten první soubor). Proč se funkčnost z 2007, fungující i v 2019 pak náhle v 2007 ztratí - a objeví se zase v 2019. Přitom je to pořád tentýž soubor.

Nerieš
Proste 2007 mal problém zo zdrojom pre rozbaľovací zoznam a vtedy sa dejú veci medzi nebom a Zemou.
To si píš, že sa na tú 12-ku zastavím!

Trochu sem se v tom ještě povrtal a udělal nějaký úpravy, při případném přepisu existující hodnoty ve sloupci A následně docházelo k chybě.