Programming Ideas

  • Thread starter Thread starter brian
  • Start date Start date
B

brian

I have a 2 item wishlist of items that could be done with
programming. I use a construction estimate workbook that
can have several estimates for one project in the book and
some of the estimates can be pretty long (9 or 10 printed
pages).

1. Can I create a button that displays the value of a
cell (With a short description). It would be kind of like
the watch box but a lot easier to read and docked onto the
toolbar in the size of a drop-down button.

2. Can a background be added to a sheet so that I can
differentiate sheets when looking at it? All estimate
sheets tend to look the same when you have 6 of them and
it is 15 minutes before the bid. I'd like a sheet name or
something to show up kind of like a watermark in the
background. It would have to move with the scrolling or
be repeated as you go down the sheet.
 
Brian,

For your second question
Use Format > Sheet > Background and find a picture or whatever.
This will be duplicated across your worksheet.
Or use cell shading.

For the first
Dim msg as String
msg = MsgBox("This is my value" & Chr(10) & Range("A1").Value, _
vbOKOnly)

Assign the macro to the button.
 
1. I was actually hoping the value of a cell could be
displayed on a button in a toolbar, above the worksheet,
where it will always be there. It would say "Cost:
{value}" and would be updated during each calculation.
When I make a change to my estimate, I need to watch the
total cost value to see how it is affected by my change.
I was hopeful that Excel's "watch box" would work for me,
but it is not quite right because it show the cell address
instead of something easier to remember).

2. Word Art works ok for individual watermarks, but it
would be nice if the watermark text could be updated when
the sheet name changes (or using some type of control).
It would be tedious to set up WordArt watermarks every
time I set up an estimate sheet because you have to put 3
or 4 of them on each page so that you see it where-ever
you are in the estimate.

TIA

Brian
 
Brian,

You could put code into a worksheet calculate event macro that would change
the caption on a toolbar button. This one would go into the ThisWorkbook
module and would work for all sheets.

For arguments sake - say the value you want is in cell Sheet1!A1
You will need to know the name of the toolbar and the position of the
button. So we will make a new toolbar with only one button.

See if these work for you.
Caution: watch for word wrap. Also if you copy and paste this code,
compile it with the Debugger because extraneous spaces sometimes show up.

================================================
' Macro to create the toolbar and the button when the workbook opens

Private Sub Workbook_Open()
Dim cbrCommandBar As CommandBar
Dim cbcCommandBarButton As CommandBarButton
Dim cst as Double

' Remove flickering and improve speed
Application.DisplayAlerts = False
Application.ScreenUpdating = False

' remove toolbar before recreating it.
'(this line can be deleted later if you want to have
'the button there all the time.
On Error Resume Next
Application.CommandBars(" Cost ").Delete
On Error GoTo 0

' add the bar
Set cbrCommandBar = _
Application.CommandBars.Add
cbrCommandBar.Name = " Cost "

cst = Sheets("Sheet1").Range("A1")
' Add command button control to the control's
' collection of CommandBar objects.
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.Caption = " Cost: " & cst
End With
End With

cbrCommandBar.Visible = True
cbrCommandBar.Position = msoBarTop

Set cbrCommandBar = Nothing
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

''' Macro to change button caption after each update
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim cst as Double
Application.DisplayAlerts = False
Application.ScreenUpdating = False

cst = Sheets("Sheet1").Range("A1")
Application.CommandBars(" Cost ").Controls(1).Caption = _
" Cost: " & cst

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

''' Macro to delete the button when the workbook
' is closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.CommandBars(" Cost ").Delete
End Sub
 
Back
Top