A Challenge - Adding Buttons to an Excel Worksheet

[Lothdyn]

Hi Everyone,

I've got a challenge I've been trying to work through today, and any ideas would be welcome. I have the following code:

1) Create a list of all the sheets in a workbook


Sub ListSheets()
Dim Mysheet As Object

ActiveSheet.Range("A1").Activate

For Each Mysheet In Sheets
ActiveCell.Value = Mysheet.Name
ActiveCell.Offset(1, 0).Activate
Next Mysheet


End Sub

2) Create buttons and code to jump to all the worksheets in the book, on the active sheet


Sub AddSheetAndButtons2()
Dim Code As String, Count As Integer
Dim NewButton As OLEObject
Dim NewSheet As Worksheet
Dim Mysheet As Object, LinkToSheet As String, NextLine As Integer

ActiveSheet.Range("A1").Activate

Do Until ActiveCell.Value = ""

Count = Count + 1
LinkToSheet = ActiveCell.Value

'Add a new button
Set NewButton = ActiveSheet.OLEObjects.Add("Forms.CommandButton.1")

NewButton.Name = "CB" & Count

With NewButton
.Left = 10
.Top = 28 * Count
.Width = 150
.Height = 25
.Object.Caption = LinkToSheet
End With

'Add Code
Code = ""
Code = "Private Sub CommandButton" & Count & "_Click()" & vbCrLf
Code = Code & vbTab & "ActiveWorkbook.Sheets(" & Chr(34) & LinkToSheet & Chr(34) & ").Activate" & vbCrLf
Code = Code & "End Sub"

'Insert code into module
With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
NextLine = .CountOfLines + 4
.InsertLines NextLine, Code
End With

ActiveCell.Offset(1, 0).Activate

Loop

End Sub

Unfortunately after the workbook creates the first button succesfully. I get a subscript out of range error when I identify the VBComponents index.

I'm hoping someone out there can suggest where I'm going wrong.

Thanks for your help

Lothdyn

EDIT:

I just realised, a version of Excel would likely help. I'm trying to get this working in Excel 2000 (or failing that 2004).

[Hack]

Moved to Office Development

[RobDog888]

Probably since you are using ActiveSheet which may or may not be the sheet that has a button on it. You should reference the sheets either directly by name or by setting an object variable to each sheet needed to be modified. Then there will be no doubt which sheet your working with.

[Lothdyn]

Thanks for the responses.

Unfortunately moving to office development is not an option as I'm a business analyst, not a programmer (hence the use of VBA).

I've had trouble when setting the sheet. For example, if I'm trying to set up sheet1 I use the commands (say



set newsheet = sheet1

With ActiveWorkbook.VBProject.VBComponents(newsheet.Name).CodeModule
NextLine = .CountOfLines + 4
.InsertLines NextLine, Code
End With


When using this code, I still end up the error. I'll give it another go a bit later.

Lothdyn

[Webtest]

Lothdyn ...

What is the maximum number of sheets you will be dealing with? One thing you can do to make this much easier is to design in the buttons into the original workbook and install the code, but disable them and set them not visible. Then, when you run the "ListSheets" code, you can just enable the buttons and make them visible. That is easy ... you don't have to dynamically load the code for the event handlers.

I have never played with controls before, and tried out your interesting routines. The error I get (Excel 2003) is:

Run-time error '1004':
Programmatic access to Visual Basic Project is not trusted

I have no idea what this means or how to get around it except to "design in" the controls and code from the beginning. If you decide to try this approach and have problems, I'm sure we can help achieve that.

[RobDog888]

Since your trying to access the vbproject of the workbook you need to manually check the "Trust access to your Visual Basic Project" in the Tools > Macro > Security dialog.

[Robbo]

hi was wondering if you can help

ive a excel sheet which emails from VBA inside

however because i use Access 2003 it comes up with a box to say running VBA do i wish to send, ive looked at the tab for trust VBA however it is greyed out

basically want to auto email and not have to press yes everytime is there a way around this, cant tick the trust VBA as its greyed out, is there a way or changing the regitry so it isnt or add to stop the message appear

thanks in advance

could you email a reply to [email protected]

ok thanks

[westconn1]

robdog has some code to work around this issue, check his signature or tutorial, also take out your email address

also a new thread with appropriate title would have been better

[Robbo]

rite ok

http://www.vbforums.com/showthread.php?t=402086

have found this, but it doesnt tell me how to get round it?? could u put the link in where it does?

i know what the problem is just cant get round it??

i havent got the trust VBA as its greyed out! its fig 3 on that link by the way, has a slight delay

havent a clue how to setup a trust thing, as its from a excel spreadsheet(VBA)

please help thanks

p.s what is the default VBA instance mean??

heres my code

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

With OutMail
.to = EmailAddr
.CC = ""
.BCC = ""
.Subject = Subj
.Body = BodyText
'.Attachments.Add ActiveWorkbook.FullName
'.Display 'or use
.send
End With

[Robbo]

hi added the trusted cerificate publisher however it still comes up with the box

please help