Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 17
Default Set up OnAction of a Menu Bar button to a function with variable in the parameters

I'm trying to create a menubar which will set the onaction property of
each item to the same function. The only difference between each
button will be the variable it passes into it (which needs to be which
worksheet to use). Here is my code below so you can see what I'm
trying to do. The line with the "*" is the one which has the compile
error. I know it's wrong but I'm not sure how to change it.

Sub CreateMenubar()
Dim iCtr As Integer
iCtr = 0
Dim CapNames As Variant
Dim MenuObject As CommandBarPopup
Dim ws As Worksheet

Call RemoveMenubar

CapNames = Array()
ReDim CapNames(Sheets.Count - 1)
For Each ws In Worksheets
CapNames(ws.Index - 1) = ws.Name
Next

Set MenuObject =
Application.CommandBars(1).Controls.Add(Type:=msoC ontrolPopup, _
Befo=11, Temporary:=True)
MenuObject.Caption = MenuBarName

For iCtr = LBound(CapNames) To UBound(CapNames)
With MenuObject.Controls.Add(Type:=msoControlButton)
*.OnAction = "'" & ThisWorkbook.Name & "'!" &
"DecisionTree(" & Sheets(CapNames(iCtr)) & ")"
.Caption = CapNames(iCtr)
End With
Next iCtr
End Sub

Sub DecisionTree(ws As Worksheet)

Application.ScreenUpdating = False

MsgBox ("Welcome to the " & StrConv(ws.Name, vbProperCase) & "
decision tree.")
Cells(ws.Columns(1).Find(What:="A", LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Row, 2).Select
ws.Cells(2, 2).Select

Do While ActiveCell.Offset(0, 3) = ""
If MsgBox(ActiveCell.Value, vbYesNo) = vbYes Then
Cells(FindIt(1, ws), 2).Select
Else
Cells(FindIt(2, ws), 2).Select
End If
Loop

MsgBox (ActiveCell.Value)
Application.ScreenUpdating = True
End Sub

Any help is much appreciated. Thanks!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Variable Text on a Button PaulW Excel Discussion (Misc queries) 1 May 10th 06 12:39 PM
Custom functions calculating time arguments Help Desperate Bill_De Excel Worksheet Functions 12 April 25th 06 02:22 AM
Built-in menu on paste button Richard Setting up and Configuration of Excel 3 September 30th 05 02:34 PM
What is the "TextBridge" menu button used for? KenLeeSmith Excel Worksheet Functions 1 November 8th 04 04:20 PM
change function variable prompts?? thinkingfield Excel Worksheet Functions 1 November 8th 04 04:01 PM


All times are GMT +1. The time now is 10:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"