Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - ComboBox wywoływane przez DoubleClick

Leon M · Wysłany: 10-05-2022, 16:27 **ComboBox wywoływane przez DoubleClick**

Szanowni Forumowicze,

Zwracam się do Was z gorącą prośbą o pomoc w rozwiązaniu poniższego zadania:

W arkuszu znajduje się tabela, podwójne kliknięcie w komórkę określonego wiersza z pierwszej kolumny powoduje otwarcie Combobox w polu klikniętej komórki. Combo zawiera listę składającą się z dwóch kolumn. Po wybraniu danej pozycji z listy, do komórki, w której otwarte jest Combo, zostaje wprowadzona wartość z pierwszej kolumny listy z wybranej pozycji. Następnie Combo zostaje zamknięte (znika).

W załączeniu zamieszczam plik obrazujący przedstawione zadanie.

Za odpowiedzi z góry bardzo dziękuję.

ComboBox wywoływane przez Doubleclick.xlsm

Pobierz Plik ściągnięto 22 raz(y) 17.09 KB

Tajan · **Pomógł:** 5234 razy Posty: 11414

Przykładowe rozwiązanie w załączniku. Opiera się na jednej kontrolce ComboBox1, która jest wyświetlana za pomocą podwójnego kliknięcia w aktywnej komórce pierwszej kolumny tabeli. Lista wartości dla combo jest obliczana za pomocą formuły:

Kod:

=PRZESUNIĘCIE(Arkusz1!$K$5;0;0;ILE.NIEPUSTYCH(Arkusz1!$K:$K);3)

do której przypisano nazwę "LISTA" i która dynamiczne reaguje na zmianę liczby wierszy.

Kod to trzy procedury zdarzeniowe w module kodu arkusza:

Kod:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim cmb As ComboBox
Dim tbl As ListObject

Set cmb = Me.ComboBox1
Set tbl = Me.ListObjects("Tabela1")

With Target
If Not Intersect(.Cells, tbl.ListColumns(1).DataBodyRange) Is Nothing Then
Cancel = True
cmb.Top = .Top - 1
cmb.Left = .Left
cmb.Height = .Height + 2
cmb.Width = .Width + 15
cmb.ListFillRange = "LISTA"
cmb.LinkedCell = .Address(0, 0)
cmb.Visible = True
End If
End With

End Sub

Private Sub ComboBox1_Change()
Dim cmb As ComboBox

Set cmb = Me.ComboBox1

If cmb.LinkedCell = "" Then Exit Sub

With Me.Range(cmb.LinkedCell).Offset(0, 1)
If cmb.ListIndex > -1 Then
.Value = cmb.Column(2)
Else
.Value = ""
End If
End With

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cmb As ComboBox

Set cmb = Me.ComboBox1

cmb.Visible = False
cmb.LinkedCell = ""

End Sub

W sumie nie przyłożyłem się zbytnio do zadania i nie jest spełnione założenie:

Cytat:

Następnie Combo zostaje zamknięte (znika).

W przykładzie kontrolka nie znika automatyczne po wybraniu wartości lecz po zmianie aktywnej komórki. Tak jest prościej :-)

ComboBox wywoływane przez Doubleclick(tj).xlsm

Pobierz Plik ściągnięto 22 raz(y) 23.92 KB

Leon M · Wysłany: 11-05-2022, 10:10

Tajan, bardzo bardzo dziękuję za przedstawiony kod. Jestem pod wielkim wrażeniem jego pomysłowości. :-)

Chciałbym jeszcze dopytać o funkcjonowanie kodu.

W poniższym fragmencie:

Kod:

With Me.Range(cmb.LinkedCell).Offset(0, 1)
If cmb.ListIndex > -1 Then
.Value = cmb.Column(2)
Else
.Value = ""
End If
End With

Następuje wstawienie wartości do kolumny 2 tabeli, czyli do komórki sąsiedniej po prawej względem LinkedCell,
ale nie mogę dociec, w którym miejscu kodu następuje wstawienie wartości do kolumny 1 tabeli, tj. do LinkedCell.

I jeszcze jedna rzecz: dlaczego liczba wstawiona do kolumny 1 formatowana jest jako tekst? Czy można uniknąć takiego formatowania?

Za odpowiedzi z góry bardzo dziękuję.

P.S.

Cytat:

W sumie nie przyłożyłem się zbytnio do zadania i nie jest spełnione założenie:
Cytat:
Następnie Combo zostaje zamknięte (znika).

Tajan, zdaje się, że zaradziłem Twojemu "nieprzyłożeniu się zbytniemu", wstawiając "cmb.Visible = False" do Sub ComboBox1_Change() i combo znika po wybraniu wartości. :-)

Tajan · **Pomógł:** 5234 razy Posty: 11414

Leon M · Wysłany: 13-05-2022, 09:36

Tajan, bardzo dziękuję za dalsze wyjaśnienia w temacie niniejszego wątku.
Powiem, że pochwalam takie prowokacje, które nasilają walor dydaktyczny prowadzonego wykładu, a ucznia nakłaniają do wytężenia swych szarych komórek. :-)

Chciałbym zwrócić się o pomoc w jednej jeszcze kwestii:

Otóż dopisałem dwie linie kodu, które powodują, że po zatwierdzeniu wartości z listy następuje dodatkowo umieszczenie w kolumnie 3 tekstu "sekcja nr 1" i zaznaczenie komórki w kolumnie 4, do której wartość wprowadzana jest manualnie.
Chciałbym rozbudować procedurę o następującą funkcjonalność:
Po wprowadzeniu wartości do kolumny 4 i jej zatwierdzeniu, następuje automatyczne otwarcie ComboBox w wierszu poniżej w kolumnie 1.

Podjąłem samodzielnie próbę rozwiązania zadania, ale niestety nieudatnie, czego ślad widoczny jest w module.

Bardzo proszę o pomoc.

W załączeniu zamieszczam plik.

ComboBox wywoływane przez Doubleclick(tj) 3.xlsm

Pobierz Plik ściągnięto 13 raz(y) 26.46 KB

Tajan · **Pomógł:** 5234 razy Posty: 11414

Nowa funkcjonalność to nieco bardziej skomplikowana sprawa. Zgodnie z logiką należy tu wykorzystać procedurę zdarzeniowa Worksheet_Change. Jednak tutaj pojawia się problem z przeniesieniem zaznaczenia do pierwszej kolumny gdyż zmiana w czasie wykonywania tej procedury jest ignorowana przez Excela i po zakończeniu procedury aktywowana jest komórka do której Excel standardowo przechodzi po zakończeniu edycji. Z tego względu proponuję następujące rozwiązanie:

Kod:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tbl As ListObject

If Target.Count > 1 Then Exit Sub

Set tbl = Me.ListObjects("Tabela1")

With Target
If Not Intersect(.Cells, tbl.ListColumns(4).DataBodyRange) Is Nothing Then
Application.OnTime Now, "'NextRecord """ & .Address & """'"
End If
End With

End Sub

Zmiana zaznaczenia jest realizowana za pomocą procedury NextRecord, która jest uruchamiana poprzez metodę OnTime. Ten sposób uruchamiana gwarantuje, że procedura NextRecord zostanie uruchomiona po zakończeniu procedury Worksheet_Change i ustawieniu aktywnej komórki.
Ze względu na wymogi metody OnTime procedura NextRecord została umieszczona w Module1 i jest skonstruowana nastepująco:

Kod:

Sub NextRecord(trg As String)
Dim thisRec As Range
Dim nextRec As Range

Application.EnableEvents = False

With Arkusz1

Set thisRec = .Range(trg)

With .ListObjects("Tabela1").ListRows
If Not Intersect(thisRec, .Item(.Count).Range) Is Nothing Then .Add
End With

Set nextRec = thisRec.Offset(1, -3)

nextRec.Select

.Worksheet_BeforeDoubleClick nextRec, True

End With

Application.EnableEvents = True

End Sub

Jej zadaniem, oprócz przeniesienia zaznaczenia i otwarcia combo, jest dodanie wiersza do tabeli w przypadku edycji ostatniego wiersza. Otwarcie kontrolki następuje poprzez wywołanie procedury Worksheet_BeforeDoubleClick z modułu Arkusz1. Z tego względu zakres widoczności tej procedury zmieniłem z Private na Public aby była widoczna w Module1.
Dodatkowo, w module Arkusz1, dodałem zmienną noChange aby wyeliminować zbędne wywołanie procedury ComboBox1_Change w czasie konfigurowania combo w procedurze Worksheet_BeforeDoubleClick.

ComboBox wywoływane przez Doubleclick(tj2).xlsm

Pobierz Plik ściągnięto 18 raz(y) 28.08 KB

Leon M · Wysłany: 14-05-2022, 13:17

Tajan, bardzo bardzo dziękuję za przedstawiony kod rozbudowujący aplikację o nową funkcjonalność oraz za szczegółowy opis jego działania.
Sposób rozwiązania postawionej powyżej kwestii jest jak dla mnie rewelacyjnie przemyślny. Nie będę ukrywał, że rzecz przyprawiła mnie o podziw dla Twej inwencji twórczej i programistycznych umiejętności.

Chciałbym dopytać nieco o funkcjonowanie kodu.

Kod:

Application.OnTime Now, "'NextRecord """ & .Address & """'"

Następuje tutaj wywołanie procedury NextRecord z argumentem Target.Address, jest natomiast dla mnie niewiadomą układ zastosowanych znaków cudzysłowu i apostrofu.

Druga kwestia to, czy zastosowane w procedurach "Me." jest substytutem instancji skoroszytu.

I jeszcze, czy można zlikwidować poziomy ScrollBar listy Combo przy szerokości kolumny1 tabeli 150 pikseli?

Ponadto zauważyłem, że procedura zamieszczona w Module1 NextRecord nie jest widoczna w Menadżerze Makr. Dlaczego?

Jest jeszcze jedna rzecz w niniejszym temacie, w kwestii której chciałbym zagadnąć.
Otóż mam kilka plików, w których zbudowana przez Tajana aplikacja mogłaby znaleźć wielce użyteczne zastosowanie.
Ale na drodze do zastosowania w nich aplikacji w aktualnej postaci stoi to, że wykorzystywane są tam tabele nakreślone metodą manualną (nie są to standardowe tabele excelowskie). Dlatego też ciekawi mnie, w jaki sposób należałoby zmodyfikować odpowiednie części kodu, aby funkcjonował nie w standardowej tabeli, ale w zwykłych komórkach. Dodatkowo jeszcze, gdyby dane do Listy ComboBox były obsługiwane nie przez formułę nazwaną, lecz zostały zapisane w tablicy vba.

Za odpowiedzi z góry bardzo dziękuję.

P.S.
Zamieszczony w załączeniu plik zawiera procedurę, która wypełnia tablicę wartościami dla ComboBox.List

ComboBox wywoływane przez Doubleclick(tj2) (b).xlsm

Pobierz Plik ściągnięto 15 raz(y) 29.73 KB

Leon M · Wysłany: 14-05-2022, 15:00

Szanowni Forumowicze,

Proszę o pomoc w następującej kwestii:

Przeniosłem procedury ułożone przez Tajana do innego pliku i pojawił się problem.
Otóż w nowym skoroszycie kompilator zgłasza błąd " User-defined type not defined",
wskazując na linię"

Kod:

Dim cmb As ComboBox

Jak się okazuje nie jest dostępna możliwość zadeklarowania zmiennej jako typ ComboBox.
W jaki sposób zaradzić temu problemowi?

Za odpowiedzi z góry bardzo dziękuję.

Leon M · Wysłany: 14-05-2022, 15:31

Szanowni Forumowicze,

Sprawa załatwiona!

Miałem chwilowe, mam nadzieje, zaćmienie umysłu. Nie utworzyłem w arkuszu ComboBox. :-)

Przepraszam za zamieszanie i niepotrzebny alarm.

Tajan · **Pomógł:** 5234 razy Posty: 11414

Leon M, zadajesz sporo pytań. Mam nadzieję, ze odpowiem na wszystkie. A zatem:

Leon M · Wysłany: 15-05-2022, 14:36

Tajan, bardzo dziękuję za tak szczegółowe wyjaśnienia i wskazówki. Przekazałeś tym sposobem pokaźną porcję wielce przydatnej wiedzy. Jeszcze raz wielkie dzięki.

Wedle Twych wskazówek przebudowa aplikacji na działanie w zwykłych obszarach arkusza faktycznie nie jawi się nader skomplikowanie, i podobnie jeśli chodzi o zastosowanie tablicy do przekazania pozycji do listy Combo, więc sądzę, że rzecz zdołam zmodyfikować samodzielnie.
Na marginesie mówiąc, rozwiązanie z funkcją zwracającą tablicę z pozycjami do listy jest bardzo pomysłowe i istotnie upraszcza kwestię.

Chciałbym jeszcze zapytać, czy przebudowa aplikacji z ComboBox na ListBox będzie polegała tylko na zmianie tych fragmentów kodu, które odnoszą się do Combo, na analogiczne odnoszące się do List Box, czy rzecz będzie wymagała jeszcze jakichś dodatkowych zabiegów.

Za odpowiedź z góry bardzo dziękuję.

Tajan · **Pomógł:** 5234 razy Posty: 11414

Myślę, że nie będą potrzebne jakieś duże zmiany. Oczywiście, na początek typ zmiennej jako MsForms.ListBox i "ręczne" określenie wysokości kontrolki.

Leon M · Wysłany: 18-05-2022, 10:55

Tajan, dziękuję za ostatnie podpowiedzi, a ogólnie jeszcze raz wielkie dzięki za aplikację i cały materiał dydaktyczny przedstawiony w niniejszym wątku.

Leon M · Wysłany: 18-05-2022, 19:20

Nie wiem, czy powinienem założyć nowy wątek dla pytania, które chcę zadać, ale na razie spróbuję postawić je tutaj.

Otóż przystąpiłem do próby przebudowy aplikacji Tajana na wersję z ListBox i już na samym początku pojawił się problem.
w procedurze Sub Worksheet_BeforeDoubleClick zadeklarowałem zmienną typu ListBox i przypisałem do niej LstBox1:

Kod:

Dim lstb As ListBox
Set lstb = Me.ListBox1

po wywołaniu pojawia się komunikat o błędzie:

Run-time error 13
Type mismatch

W czym tkwi przyczyna błędu? Bardzo proszę o pomoc.
Za odpowiedzi z góry bardzo dziękuję.

Tajan · **Pomógł:** 5234 razy Posty: 11414

Jak pisałem wyżej, zadeklaruj:

Kod:

Dim lstb As MsForms.ListBox

Ponadto, dla ListBoxa, proponuję użyć zdarzenia DoubleClick zamiast Change.