Symulowanie RefEdit   strona główna:
A po co ten Excel ;-)
 
    Dzisiejszym tematem będzie wskazywanie zakresu do kontrolki na UserForm'ie.  
Standardowo używa się do tego kontrolki RefEdit.ctrl ale że sprawia ona spore kłopoty wie prawie każdy który ją używał. Z tego  
również powodu nie proponuje się jej do rozwiązywania zadań tego typu zadań. Klika cytatów rozmów powinno dobrze nakreślić  
sytuację:  
 
    domizawa >>pojawił się problem (..) "Compile error: Can't find project or library" (..)<<  
    Tajan MVP >>Moim zdaniem zerwane referencje. Sprawdź w menu edytora VBA, czy w opcji Tools/References    źródło cytowanego fragmentu
nie masz pozycji oznaczonych jako "Missing".(..) w Twoim pliku mam "Missing:RefEdit Control".   
Po odznaczeniu tej pozycji userform startuje normalnie. Ta kontrolka jest raczej rzadko używana (..)   
To jest dość częsty problem, który zaliczyłbym do bugów Excela, <<  
 
    dwalczuk >>dokument otwarty raz na Excelu2007, w poprzednich wersjach traci swoją funkcjonalność tzn.   
po wprowadzeniu jakichkolwiek zmian nie daje się zapisać, a przy próbie otwarcia Module w edytorze VBA   
dostaję komunikat o błędnym pliku *.dll. (..) w Tools\References: MISSING: Ref Edit Control   
Oczywiście RefEdit.dll istnieje w podanej lokalizacji C:\Program Files\Microsoft Office\OFFICE11 <<  
    Artik >>Jest prawie pewne, że problem jest z kontrolką RefEdit. Spróbuj wyrejestrować kontrolkę z systemu. W wierszu poleceń:    źródło cytowanego fragmentu
regsvr32 /u "C:\Program Files\Microsoft Office\OFFICE11\RefEdit.dll"   
i ponownie ją zarejestrować:   
regsvr32 "C:\Program Files\Microsoft Office\OFFICE11\RefEdit.dll"   
Powinieneś otrzymać odpowiedź typu:   
Funkcja DllRegisterServer w C:\Program Files\Microsoft Office\OFFICE11\RefEdit.dll powiodła się <<   
 
    Wiedząc że takie "cuda" mogą czekać użytkownika naszego formularza ciężko dziwić się że kontrolka jest raczej nie używana, ale  
potrzeba pozostaje. Czym więc ją zastąpić??  
    Jedną z możliwości jest zastosowanie Metody Application.InputBox z Param. Type:=8 (A cell reference, as a Range object)   InputBox Method (msdm)
A więc tymczasowe ukrycie formularza i wyświetlenie okna InputBox'a z możliwością wskazania zakresu.  
Przykład zastosowania tej techniki wraz z przykładem do ściągnięcia można znaleźć na stronach Peltier Tech Blog (link z prawej)   Alternative to Excel’s Flaky
RefEdit Control
Peltier Tech Blog
 
Mam jednak inny pomysł. Można spróbować symulować działanie kontrolki RefEdit przystosowując przeznaczony do tego TextBox  
oraz Formularz na którym się on znajduje. I o tym właśnie będzie ten temat. :-)  
 
Zatem najpierw dwa słowa nt. działania RefEdit. Kontrolka podobna do ComboBox'a z ustawioną właściwością DropButtonStyle na:  
3 - fmDropButtonStyleReduce.  Po kliknięciu na DropButton formularz zostaje zredukowany tak że widać jedynie naszą kontrolkę.  
Forma staje się modalna tak że można zaznaczyć zakres (dowolny, również nieciągły zakres, w dowolnym arkuszu). Po zaznaczeniu  
zakresu w kontrolce ukazuje się Adres zakresu poprzedzony nazwą Arkusza. Po kolejnym kliknięciu na DropButton Formularz wraca  
do pierwotnego wyglądu. Więc na dobrą sprawę jedynie zmiana modalności formularza jest zadaniem ciekawym reszta raczej prosta.  
Do roboty więc... :-)  
Zamiast załączać cały kod i omawiać fragmentami będę podawał kolejne części   
i je omawiał co odzwierciedli proces tworzenia naszej kontrolki.  
 
Na UserForm'ie umieszczamy dowolne kontroli w dowolnych miejscach. Pomiędzy nimi ma się znaleźć TextBox który będzie naszą  
RefEdit. Nazwijmy go tak!. W oknie Properties (VBE / F4) we właściwości (Name) naszego TextBox'a wpiszmy RefEdit.  
 
Musimy odrobinę dostosować naszą RefEdit już podczas ładowania UserForm'a.  
 
Private Sub UserForm_Initialize()  
    With Me.RefEdit  
        .DropButtonStyle = fmDropButtonStyleReduce  
        .ShowDropButtonWhen = fmShowDropButtonWhenAlways  
        .Tag = "MojeRefEdit"  
    End With  
    NormalPoz = GetNormalCtrPoz(Me.RefEdit)  
End Sub  
 
No ale chwila chwila ... Jak wpiszemy fragment With Me.RefEdit to w następnych linijkach zaczynając od kropki będą dostępne  
właściwości, metody naszego TextBox'a i nie ma tam takich właściwości jak .DropButtonStyle czy .ShowDropButtonWhen. Co znaczy nie ma -   
Edytor VBA ich nie podpowiada :-(   
Ale jednak są. :-) Tyle że w "HiddenMembers". Klikamy F2 (lub Zakładka: Viwe / Object Browser) i w oknie które teraz przykrywa nam okno kodu:  
Prawy Prz. Myszy / klikamy na: Show Hidden Members i zamykamy okno. Sprawdźmy teraz czy dyskusyjne właściwości są dostępne (?) Są :-)  
(Też byłem tym zachwycony :-P Warto przyjrzeć te właściwości choć nie tylko TextBox'ów. Jest tam wiele ciekawych rzeczy ale o tym może kiedy indziej)  
A więc TextBox'owi można nadać styl Drop Button'a - niesamowite :lol: Co ciekawe właściwości tej nie można nadać inaczej niż poprzez VBA  
    Nadamy naszej kontrolce właściwość Tag - później się przyda.  
    Trzeba jeszcze wyjaśnić linię: NormalPoz = GetNormalCtrPoz(Me.RefEdit)  
Chodzi o zapisanie określonych właściwości zarówno naszej RefEdit jak i całego formularza. Będzie nam to potrzebne gdyż pozycja kontrolki będzie się  
zmieniać i będziemy musieć wiedzieć gdzie ją z powrotem umieścić po pobraniu adresu zakresu. Będziemy również zmieniać wygląd formularza. Po  
wykonaniu zadania trzeba będzie rozszerzyć formularz do poprzednich rozmiarów. I te rozmiary również musimy zapamiętać.  
 
Jakie właściwości będą nam potrzebne:  
właściwości UserForma: Caption, Height, Width; właściwości RefEdit: Top, Left, Height i Width  
Będziemy je przechowywać w jednej zmiennej poziomu moduły formularza przechowującej strukturę (User Defined Type) z wszystkimi wymienionymi  
właściwościami.  
 
Private Type CtrInfo  
    strFormCaption As String  
    sFormHeight As Single  
    sFormWidth As Single  
    sCtrTop As Single  
    sCtrHeight As Single  
    sCtrLeft As Single  
    sCtrWidth As Single  
End Type  
 
Private NormalPoz As CtrInfo  
 
Private Function GetNormalCtrPoz(objCtr As Control) As CtrInfo  
    With objCtr  
        GetNormalCtrPoz.strFormCaption = .Parent.Caption  
        GetNormalCtrPoz.sFormHeight = .Parent.Height  
        GetNormalCtrPoz.sFormWidth = .Parent.Width  
        GetNormalCtrPoz.sCtrHeight = .Height  
        GetNormalCtrPoz.sCtrLeft = .Left  
        GetNormalCtrPoz.sCtrTop = .Top  
        GetNormalCtrPoz.sCtrWidth = .Width  
    End With  
End Function  
 
Teraz obojętnie gdzie wstawimy TextBox'a na Formularzu po zmianie jego położenia, będącego konsekwencją odwzorowywania działania  
kontrolki RefEdit, po pobraniu zakresu będziemy wiedzieć gdzie kontrolka była umieszczona i jak wyglądał formularz przez redukcją.  
 
Następną kwestią jest oprogramowanie kliknięcia na DropButton naszej RefEdit:  
 
Private Declare Function FindWindow _  
    Lib "user32.dll" _  
        Alias "FindWindowA" ( _  
        ByVal lpClassName As String, _  
        ByVal lpWindowName As String) _  
    As Long  
 
Private Declare Function EnableWindow _  
    Lib "user32" ( _  
        ByVal hWnd As Long, _  
        ByVal fEnable As Long) _  
    As Long  
 
Private mbModal As Boolean  
 
Private Sub RefEdit_DropButtonClick()  
    SetPoz Not mbModal  
    bSelectionFlag = Not mbModal  
    EnableWindow hWndMain, Abs(CInt(Not mbModal))  
    mbModal = Not mbModal  
End Sub  
 
Private Function hWndMain() As Long  
    hWndMain = FindWindow("XLMAIN", Application.Caption)  
End Function  
 
Krótkie, ale nie łatwe..   
 - mbModal to kolejna zmienna poziomu modułu formularza która przechowuje informację o fakcie czy forma w danej chwili jest modalna czy nie.  
Przed pierwszym kliknięciem na DropButton zmienna ta będzie miała wartość (domyślną) False.  
 - SetPoz Not mbModal uruchomi procedurę SetPoz z parametrem Not modal = (Not False) True  
 
Private Sub SetPoz(Optional bFlag As Boolean = False)  
    Dim ctr As Control  
    If Not bFlag Then  
        With Me  
            .Caption = NormalPoz.strFormCaption  
            .Height = NormalPoz.sFormHeight  
            .Width = NormalPoz.sFormWidth  
            With .RefEdit  
                .Top = NormalPoz.sCtrTop  
                .Height = NormalPoz.sCtrHeight  
                .Left = NormalPoz.sCtrLeft  
                .Width = NormalPoz.sCtrWidth  
            End With  
        End With  
    Else  
        With Me  
            .Caption = "Wskaż zakres..."  
            .Height = 42  
            .Width = 202  
            With .RefEdit  
                .Top = 0: .Height = 20  
                .Left = 0: .Width = 200  
            End With  
        End With  
    End If  
    For Each ctr In Me.Controls  
        If ctr.Tag <> "MojeRefEdit" Then ctr.Visible = Not bFlag  
    Next  
End Sub  
 
W tej procedurze wykona się fragment po Else.. Redukcja Formularza do .Height = 42, i .Width = 202. Oraz analogicznie RefEdit:  
.Top = 0, .Left = 0 (lewy górny róg UserForm'a) .Height = 20, .Width = 200. Kontrolka trochę mniejsza niż cały formularz.  
Wykona się również pętla po wszystkich kontrolkach UserForm'a. Ukrywająca/Odkrywająca wszystkie kontrolki (oprócz naszej - rozpoznanej dzięki wł. Tag)  
Teraz wykona się ukrywanie ponieważ bFlag = True  
 
 - bSelectionFlag = Not mbModal Zasadnicze pytanie brzmi: Jak do kontrolki przekazać Adres zaznaczonego zakresu?? Należy zaznaczyć zakres a więc  
zmiana zaznaczenia powinna wywoływać wypełnienie RefEdit. Zdarzenie zmiany zaznaczenia w dowolnym arkuszu należy zdefiniować w module ThisWork-  
book w zdarzeniu Workbook_SheetSelectionChange ale kiedy ma ono wstawiać do naszej kontrolki Adres Target.u? Ano wtedy gdy forma jest modalna  
i z ogóle załadowana do pamięci. Zmienna bSelectionFlag to zmienna poziomu modułu zapisana w module Standardowym. Jej wartość zostaje tu ustawiona  
na True żeby zdarzenie przekazywało do naszej RefEdit Target.Address  
    Zatem w module Thisworkbook  
 
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)  
    If bSelectionFlag Then  
        UserForm1.RefEdit = Replace(Target.Address(External:=True), _  
                                     "[" & Me.Name & "]", _  
                                     vbNullString)  
    End If  
End Sub  
 
 - EnableWindow hWndMain, Abs(CInt(Not mbModal)) Tu właśnie odbywa się zmiana modalności UserForm'a. Okazuje się że zmiana ta dotyczy   Stephen Bullen's Excel Page
właściwości Enable okna aplikacji (Excela) a nie UserForm'a. Funkcja hWndMain określa uchwyt głównego okna Excela któremu właściwość Enable jest  
ustawiona na True (zmienna mbModal jest ciągle ustawiona na False - Not False = ... )  
Linia ta pochodzi z przykładu FormFun dostępnego na stronie Stephen Bullen's Excel Page w części VBA Programming  
 
 - mbModal = Not mbModal  to ustawienie zmiennej  mbModal na True. Żeby po kolejnym kliknięciu na DropDown wszystko zostało odkręcone  :-)  
 
Pozostaje jeszcze kwestia czyszczenia zmiennych poziomu modułu. Po co? Żeby przedwczesny reset Formularza nie pozostawił ich na niepożądanej  
wartości. Może nastąpić wyłączenie formularza kiedy jest modalny, w zredukowanej postaci. Zmienne mają wtedy wartość True. I jedynie restart pliku może  
zmienić tą wartość automatycznie. A więc jeśli włączymy ponownie formularz pierwsze kliknięcie na DropDown RefEdit nie spowoduje pożądanego działania.  
Trzeba więc...  
 
Private Sub UserForm_Terminate()  
    bSelectionFlag = False  
    mbModal = False  
End Sub  
 
Przykład ten można ściągnąć z excelforum.pl (link z prawej)   przykład