What to use for floating help?

[Norm Lundquist]

Here's probably a simple question, but I don't know wheather to use a label
or text box from the "Forms" or "Control Toolbox".

I am entering simple expense data with date, description, expense code, and
amount in columns A thru D. The code is a value from 1 to 25. I would like
to see something on the right that would show me each code and what that code
stands for - like 1=Supplies; 2=Repairs. The codes and descriptions are at
the bottom of the worksheet, but I don't want to scroll down to see them; nor
print them off because I add new codes when needed.

On the Auto-Open, I was going to make a box to show on the right. I suppose
it will be a Label or a TextBox; but should I use the "Forms" or "Control
Tookbox" to create it ... what is the difference? It will need to stay in
the same place, so when the window scrolls down, will it need to change
positions?

Thanks for any and all help!

 

[Greg Wilson]

The best way IMO is to use a toolbar since it is window based (as opposed to
worksheet) and therefore is not affected by scrolling. A demo follows. You
may have to adjust the spaces that follow the string constants (T1, T2
etc...) in order to get them to line up. Since no one else answered you're
stuck we me <g>.

Const T1 As String = "1 - Equipment Repairs "
Const T2 As String = "2 - Office Supplies "
Const T3 As String = "3 - Vehicle Expense "
Const T4 As String = "4 - Equipment Rental "
Const T5 As String = "5 - Travel Expence "
Const T6 As String = "6 - Entertainment "
Const T7 As String = "7 - Bribing Clients "
Const T8 As String = "8 - Meal Expence "

'Make this Auto-Open instead or call with Workbook_Open
Private Sub MakeCodeDescripTB()
Dim CB As CommandBar
Dim btn As CommandBarButton
Dim CaptArr As Variant
Dim i As Integer

CaptArr = Array(T1, T2, T3, T4, T5, T6, T7, T8)

On Error Resume Next
Application.CommandBars("Code List").Delete
On Error GoTo 0
Set CB = Application.CommandBars.Add("Code List", _
Temporary:=True)
With CB
.Protection = msoBarNoResize
For i = 0 To UBound(CaptArr)
Set btn = .Controls.Add
With btn
.Style = msoButtonCaption
.Caption = CaptArr(i)
.OnAction = "GetCodeDescrip"
.Width = 100
End With
Next
.Left = 100
.Top = 100
.Width = 100
.Height = (i + 1) * 25
.Visible = True
End With
End Sub

Private Sub GetCodeDescrip()
Dim btn As CommandBarButton

Set btn = Application.CommandBars.ActionControl
Dim txt As String

Select Case btn.Caption
Case T1
txt = T1 & vbCr & vbCr & _
"Defintion: This expence means... "
Case T2
txt = T2 & vbCr & vbCr & _
"Definition: This expence means... "
Case T3
txt = T3 & vbCr & vbCr & _
"Definition: This expence means... "
Case T4
txt = T4 & vbCr & vbCr & _
"Definition: This expence means... "
Case T5
txt = T5 & vbCr & vbCr & _
"Definition: This expence means... "
Case T6
txt = T6 & vbCr & vbCr & _
"Definition: This expence means... "
Case T7
txt = T7 & vbCr & vbCr & _
"Definition: This expence means... "
Case T8
txt = T8 & vbCr & vbCr & _
"Definition: This expence means... "
End Select
MsgBox txt, vbInformation, "Code Descriptions"
End Sub

Regards,
Greg

 

[Cutter]

Have you considered Data Validation

You can put your codes in the Input Box which can be shown as soon as
your cell is selected. The Input Message box can be moved over to the
right side and it floats as you scroll down.

You can also restrict input in the cell to whole numbers between 1 and
25.

I think it will do everything you want.

 

[Greg Wilson]

Good point. I gave it some thought but it never clicked with me that it
floated. You can unselect the In-cell dropdown checkbox so that it isn't a
nuisance.

The web site stripped most of the blank spaces that followed the string
constants I used for captions. I had them lining up before I posted. I prefer
your suggestion so this is a mute point.

Regards,
Greg

 

[Norm Lundquist]

Thanks, Cutter and Greg, for your responses. I really appreciate it and am
experimenting with both. VERY MUCH APPRECIATED!!

Norm

 

[Cutter]

You're welcome. Thanks for acknowledging.