Questions about existing code

djl0525

Well-known Member
Joined
Dec 11, 2004
Messages
1,240
The code below was copied from one of John Walkenbach's CDs. It's working fine, but I have a few questions about it.

1. What does the Option Explicit do? More to the point, what does the Public Const line do? My file has nothing to do with elephants. I know I can modify it, but what will happen if I delete that line?

2. I want to close the Assistant if the workbook from which it was launched is closed. And I want to hide it if the workbook is no longer the active workbook (and then come back if the workbook becomes active again). In other words, I want the help file that is launched by this code to be context sensitive to the workbook that launched it. How do I do that?

3. Can I change the color of the help ballon from the default yellow to something else?

Thank you in advance for your consideration. Here is the code:

Option Explicit

Public Const APPNAME As String = "Elephants R Us"
Dim Topic
Dim HelpSheet
_________________________________________________________

Sub ShowHelp()
Set HelpSheet = ThisWorkbook.Worksheets("HelpSheet")
Application.Assistant.On = True
Topic = 1
With Assistant.NewBalloon
.Heading = "Help Topic " & Topic & ": " & vbCrLf & HelpSheet.Cells(Topic, 1)
.Text = HelpSheet.Cells(Topic, 2)

.Button = msoButtonSetNextClose
.BalloonType = msoBalloonTypeButtons
.Mode = msoModeModeless
.Callback = "ProcessRequest"
.Show
End With

End Sub
_________________________________________________________

Sub ProcessRequest(bln As Balloon, lbtn As Long, lPriv As Long)
Dim NumTopics As Integer

NumTopics = Application.WorksheetFunction.CountA(HelpSheet.Range("A:A"))
Assistant.Animation = msoAnimationCharacterSuccessMajor
Select Case lbtn
Case msoBalloonButtonBack
If Topic <> 1 Then Topic = Topic - 1
Case msoBalloonButtonNext
If Topic <> NumTopics Then Topic = Topic + 1
Case msoBalloonButtonClose
bln.Close
Exit Sub
End Select
With bln
.Close
Select Case Topic
Case 1: .Button = msoButtonSetNextClose
Case NumTopics: .Button = msoButtonSetBackClose
Case Else: .Button = msoButtonSetBackNextClose
End Select
.Heading = "Help Topic " & Topic & ": " & vbCrLf & HelpSheet.Cells(Topic, 1)
.Text = HelpSheet.Cells(Topic, 2)
.Show
End With
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
djl0525 said:
The code below was copied from one of John Walkenbach's CDs. It's working fine, but I have a few questions about it.

1. What does the Option Explicit do? More to the point, what does the Public Const line do? My file has nothing to do with elephants. I know I can modify it, but what will happen if I delete that line?

I don't see APPNAME anywhere else in the code. That's being declared as a constant (a variable that never changes its value, basically, oxymoronic as that sounds).

As for Option Explicit, see the VBA help file, or any number of posts in the forum, this one, for example. HTH
 
Upvote 0
Thank you for your response and for the link. I read it and as usual, I learned something.

Have a great day!

DJ
 
Upvote 0
Another question... I need to add a column and a row to the HelpSheet. In my new column A, I will number the help topics. In my new row 1 I'll put information that is not a help topic. Therefore, I don't want row 1 and column A to be displayed in the help balloons. How do I make this happen, please?

Thanks -- DJ
 
Upvote 0

Forum statistics

Threads
1,224,872
Messages
6,181,498
Members
453,047
Latest member
charlie_odd

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top