Arkusz startowy gdy makra zostały wyłączone

Na początek opis problemu:

• skoroszyt posiada dwa arkusze- MacroDisabled i MacroEnabled
• w sytuacji gdy skoroszyt jest otwierany z dostępem do makr automatycznie wyświetlony powinien zostać arkusz MacroEnabled. Ten punkt możemy uznać za prosty i oczywisty gdyż wszystko czego potrzebujemy to prosta procedura zdarzenia Private Sub Workbook_Open()
• w sytuacji gdy skoroszyt otwierany jest bez dostępu do makr zawsze wyświetlony ma być arkusz MacroDisabled. Niestety, nie możemy sięgnąć po procedurę zdarzenia gdyż makra są wyłączone.
• z tego samego skoroszytu korzystamy naprzemiennie w opcji Macro Enabled i Macro Disabled
• lista zakładek arkuszowych jest ukryta (opcjonalnie arkusze mogą być ukryte) dlatego też po otwarciu pliku w opcji MacroDisabled nie jesteśmy w stanie ręcznie przejść do arkusza MacroEnabled.

Jedyna sytuacja, która staje się problematyczna to moment, gdy po otwarciu pliku w opcji MacroEnabled (krok 1) następuje otwarcie pliku w opcji MacroDisabled (krok 2). Krok 1 otwiera arkusz Enabled lecz musi zamknąć skoroszyt w opcji Disabled. Oczywiście możemy wykorzystać zdarzenie Workbook_BeforeSave lecz należy pamiętać, że staje się to problematyczne gdy użytkownik ostatecznie postanawia wrócić do pracy i nie zamyka skoroszytu. W tej sytuacji aktywowany zostałby arkusz Disabled i dalsza praca nie mogła by być kontynuowana.

Poniżej prezentuję proponowane rozwiązanie tej sytuacji wraz z dodatkowymi komentarzami.

Krok 1. otwarcie skoroszytu z dostępem do makr (MacroEnabled). Wykorzystamy zdarzenie Open w celu aktywacji arkusza:

view sourceprint?

1	Private Sub Workbook_Open()

2

3	Sheets("MacroEnabled").Activate

4

5

End Sub

Krok 2. zapisanie arkusza w sytuacji, gdy skoroszyt został otwarty w wariancie MacroEnabled. Wykorzystamy zdarzenie Before_Save oraz dodatkową procedurę pomocniczą:

view sourceprint?

01	Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

02

03	If ActiveSheet Is Sheets("MacroDisabled") Then Exit Sub

04

05	If PoprzedniSheet Is Nothing Then

06

07	Set PoprzedniSheet = ActiveSheet

08	Sheets("MacroDisabled").Activate

09

10	'kluczowa metoda- wywoływana o ile użytkownik

11	'nie zamyka pliku a jedynie go zapisuje lub

12	'rezygnuje z zapisania/zamknięcia

13

14	Application.OnTime Now, "AktywowaniePoprzedniego"

15

End If

16

End Sub

Krok 3. w osobnym module tworzymy zmienną publiczną oraz kod przywracający aktywny arkusz:

view sourceprint?

01	Public PoprzedniSheet As Worksheet

02

03	Public Sub AktywowaniePoprzedniego()

04

05	If Not PoprzedniSheet Is Nothing Then

06	PoprzedniSheet.Activate

07	Set PoprzedniSheet = Nothing

08

End If

09

10

End Sub

Export wybranych obszarów komórek do pliku PDF

Podstawowy sposób zapisania w formacie PDF obszarów komórek Excel opiera się na wykorzystaniu metody .ExportAsFixedFormat. Warto jednak wiedzieć, że metoda ta dostępna jest dla różnych obiektów na różnych poziomach hierarchii:

• komórek- obiekt Range
• arkusza- obiekt Worksheet
• skoroszytu- obiekt Workbook

1. Obiekt Range udostępnia metodę wprost umożliwiając stworzenie instrukcji:

view sourceprint?

1	Range("A1:G10").ExportAsFixedFormat xlTypePDF, "RangeToPDF.pdf"

Warto jednak pamiętać o możliwości wykorzystania innych metod definiowania obiektu Range. Poniższy przykład również jest prawidłowy lecz co ciekawe- każdy z obszarów wewnątrz instrukcji Union zostanie zapisany na osobnej stronie dokumentu PDF:

view sourceprint?

1	Union(Range("A1:E5"), Range("a10:G20")). _

2	ExportAsFixedFormat xlTypePDF, "UnionToPDF.pdf"

Powyższa techniki nie zadziała oczywiście dla obszarów pochodzących z różnych arkuszy. W wariancie tym, co ważne, ignorowane są jednak obszary wydruku które są istotne przy eksporcie opisanym w punkcie 2 i 3 poniżej.

2. Obiekt Worksheet umożliwia wydrukowanie całego arkusza lub jego części ustawionej jako obszar wydruku:

view sourceprint?

1	Sheets(1).ExportAsFixedFormat xlTypePDF, "SheetToPDF.pdf"

3. Obiekt Workbook również posiada metodę .ExportAsFixFormat a jej wykorzystanie spowoduje eksport wszystkich ustawionych obszarów wydruku lub całych arkuszy do pliku PDF:

view sourceprint?

1	ThisWorkbook.ExportAsFixedFormat xlTypePDF, "WorkbookToPDF.pdf"

Co powinniśmy zrobić jeżeli chcemy wyeksportować do PDF różne obszary z różnych arkuszy? W takiej sytuacji musimy w pierwszym kroku ustawić obszary wydruku indywidualnie dla każdego arkusza wykorzystując poniższe dostępne techniki:

view sourceprint?

1	ActiveSheet.PageSetup.PrintArea = "$B$5:$E$14"

2	ActiveSheet.PageSetup.PrintArea = Range("B5:E14").Address

a następnie wywołać metodę eksportu dla skoroszytu.

Blokada edycji zakładek w dokumencie MS Word

Poniższy problem został wywołany niedawno na forum StackOverflow.Com i można go opisać w następujących kilku słowach:

W jaki sposób zablokować dostęp do zakładek dokumentu jednocześnie umożliwiając edycję pozostałych elementów dokumentu?

Całkiem ciekawe rozwiązanie tego problemu opiera się na wykorzystaniu obiektów ContentControl (CC) i zdarzeń powiązanych z tym obiektami. Co ważne, rozwiązanie to dostępne jest począwszy od wersji 2007 aplikacji MS Word.

Krok 1. Każdą zakładkę w dokumencie musimy umieścić wewnątrz obiektu ContentControl. Jeżeli nie chcemy wykonywać tej operacji ręcznie wystarczy uruchomić proste makro:

view sourceprint?

1	Sub Dodawanie_ContentControl_do_Zakladki()

2

3	Dim bookM As Bookmark

4	For Each bookM In ActiveDocument.Bookmarks

5	ActiveDocument.ContentControls.add wdContentControlRichText, _

6	bookM.Range

7

Next

8

9

End Sub

Krok 2. Zakładamy, że naszym celem jest uniemożliwienie edycji bez blokowania obiektu ContentControl. W tym celu wykorzystamy zdarzenie powiązane z tego typu obiektami i wymusimy zmianę zaznaczenia w sytuacji gdy użytkownik kliknie wewnątrz obiektu CC:

view sourceprint?

01	Private Sub Document_ContentControlOnEnter(ByVal ContentControl As ContentControl)

02

03	If ContentControl.Range.Bookmarks.Count > 0 Then

04	'Opcjonalna informacja dla użytkownika

05	MsgBox "Nie powinieneś zmieniać zawartości " & _

06	"tego obszaru i tej zakładki." & _

07	vbNewLine & "Zostaniesz przeniesiony poza obszar edycji"

08

09	'Opcjonalne przeniesienie poza obszar CC/Zakładki

10	Dim newPos As Long

11	newPos = ContentControl.Range.End + 2

12	ActiveDocument.Range(newPos, newPos).Select

13

14

End If

15

16

End Sub

Alternatywnie krok 1 można rozszerzyć o mechanizm blokowania edycji obiektu CC i jednocześnie zrezygnować z obsługi zdarzenia obiektów CC. W tym wypadku zamiast kroków 1 i 2 zastosujemy poniższy rozszerzony kod z kroku 1:

view sourceprint?

01	Sub Add_Bookmark_CC_Protected()

02

03	Dim bookM As Bookmark

04	Dim CC As ContentControl

05	For Each bookM In ActiveDocument.Bookmarks

06	Set CC = ActiveDocument.ContentControls.Add( _

07	wdContentControlRichText, bookM.Range)

08

09	'kluczowa w tym wariancie blokada dostępu do obiektu CC

10	CC.LockContents = True

11

Next

12

13

End Sub

Imitacja zdarzenia MouseOver (Hover) dla komórki Excela...

...czyli jak wywołać makro pod wpływem najechania wskaźnikiem myszy nad określoną komórkę Excela.

Znawcy VBA z pewnością od razu domyślają się, że nie chodzi o wykorzystanie zdarzenia gdyż do wersji Office 2013 w zasobie dostępnych zdarzeń nie znajdziemy takiego, które wywoła efekt MouseOver dla komórki Excela. Okazuje się jednak, że efekt ten możemy uzyskać połączywszy ze sobą dwa elementy:

• własną funkcję użytkownika (UDF), wewnątrz której zdefiniujemy określoną akcję wywoływaną pod wpływem najechania myszą nad komórkę
• formułę komórkową HIPERŁĄCZE (opcjonalnie połączoną z formułą JEŻELI).

Zanim zaprezentuję określony kod nakreślmy oczekiwany scenariusz:

• komórka A1 zawierać będzie tekst: "Najedź myszą w celu uzyskania dodatkowych informacji"
• komórka B1 zawierać będzie tekst: "Wyłącz dodatkowe informacje"
• najechanie myszą na komórki A1 i B1 będzie odpowiednio odkrywać i ukrywać obiekt Shape zawierający dodatkowe informacje (kształt Shape w naszym przypadku nazywa się "ShapeTip1")

Krok 1. Zacznijmy od stworzenia funkcji UDF, której zadaniem będzie pobranie parametru decydującego o odkryciu/ukryciu kształtu:

view sourceprint?

1	Function MouseOver_ShowHide(boShow As String)

2

3	If boShow = True Then

4	ActiveSheet.Shapes("ShapeTip1").Visible = True

5

Else

6	ActiveSheet.Shapes("ShapeTip1").Visible = False

7

End If

8

9	End Function

Krok 2. W arkuszu tworzymy kształt i ustawiamy jego nazwę na ShapeTip1.

Krok 3. W komórce A1 dodajemy formułę HYPERLINK wg schematu:

view sourceprint?

1	=HIPERŁĄCZE(MouseOver_ShowHide(PRAWDA);

2	"Najedź myszą w celu uzyskania dodatkowych informacji")

Z niewiadomych przyczyn formuła HIPERŁĄCZE zwracać może błąd dla powyższej konstrukcji dlatego też warto od razu obudować ją w formułę JEŻELI(CZY.BŁ(...)) (wskazówka: formuła JEŻELI.BŁĄD() niekoniecznie sprawdza się w tej sytuacji co warto przetestować).
Ostatecznie więc do komórki B1 wpisujemy następującą formułę wyzwalającą ukrywanie:

view sourceprint?

1	=JEŻELI(CZY.BŁ(HIPERŁĄCZE(MouseOver_ShowHide(FAŁSZ)));

2	"Wyłącz dodatkowe informacje";"Wyłącz dodatkowe informacje")

Ostateczny układ arkusza mógłby wyglądać jak na poniższym zrzucie ekranu. Najechanie muszą na komórkę A1 odkrywa prostokąt z komunikatem. Ukrywanie prostokąta wyzwalane jest przez najechanie myszą na komórkę B1.

Wskazówki i uwagi końcowe:

• warto zwrócić uwagę, że zatrzymanie myszy nad komórkami A1 i B1 powodują nieustanne wywoływanie utworzonej funkcji UDF co nie jest zbyt efektywne
• niestety nie udało mi się stworzyć mechanizmu autoukrywania kształtu po opuszczeniu komórki A1 bez najechania na komórkę B1. Funkcja nie wyzwala zdarzeń ani polecenia Application.OnTime.
• Inspiracją do stworzenia niniejszego wpisu były przykłady znalezione w sieci. Tu chciałbym jednak polecić Waszej uwadze doskonały arkusz oparty o podobne rozwiązanie, który prezentuje okresowy układ pierwiastków. Pod tym linkiem znajdziecie zarówno gotowy przykład pliku Excela jak również prosty filmik prezentujący działanie tego narzędzia.