Adding a new sheet with command button

**crapmind** · 08-03-2007, 02:21 AM

I just stuck on the basics. I want to add a new sheet through a macro and a button in the same. I am unable to make it dynamic. it is giving me an error:

Can anyone help me on the same:

Please Login or Register  to view this content.

**royUK** · 08-03-2007, 02:25 AM

far simpler to have a hidden template sheet that you copy when needed.

**crapmind** · 08-07-2007, 02:19 AM

didnt get u man... how to do it??

**royUK** · 08-07-2007, 04:49 AM

Just create a sheet with all the controls, formatting & formulas. Save 7 hide it then use code to create a copy when needed.

See the Summary sheet's code here

http://www.excel-it.com/workbook_downloads.htm

**crapmind** · 08-09-2007, 02:23 AM

Thanks for your help man. But

1) the website link is not working
2) Basically I need to embed the same in another macro... The original macro starts with 2 sheets... and when it starts processing it deletes all other existing sheet.. even if u manually add anything.. it creates around 8-9 sheets for processing the macro and then delete all other sheets.... Keep only the original 2 sheets and a new 1 processed sheets....

So I cant keep anything hidden.... Thats Y i want to create another macro with above functionality...

Is there any solution??

**royUK** · 08-09-2007, 02:35 AM

The link is fine.

**crapmind** · 08-11-2007, 03:07 AM

the solution is not working... can any1 help??

**royUK** · 08-11-2007, 08:20 AM

Originally Posted by crapmind

the solution is not working... can any1 help??

Spend a little time on your replies. If you bother to explain why the solution is not working then maybe you will get some help.

**corinereyes** · 08-11-2007, 09:07 AM

Hello Crapmind,

Sub abc()
Dim oWS1 As Worksheet
Set oWS1 = ActiveSheet

Sheets(oWS1).Select
Sheets.Add
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Tol"

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
, DisplayAsIcon:=False, Left:=345.75, Top:=11.25, Width:=165.75, _
Height:=40.5).Select

Set vObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=345.75, Top:=11.25, _
Width:=165.75, Height:=40.5)

Set CmdBtn = vObj.Object 'This is a read only property

CmdBtn.Caption = "Misc Info"

End Sub

I've checked your code and its working fine. From your code ---> Sheets(oWS1).Select just put " " on the sheet name --> Sheets("oWS1").Select

Hope this helps you.

corine

**bettatronic** · 08-11-2007, 09:42 AM

I think this kind of the problem has been kicked around recently. So check the threads below:

http://www.excelforum.com/showthread.php?t=609667
http://www.excelforum.com/showthread...76#post1812176

**crapmind** · 08-12-2007, 03:04 PM

1st to royUK

ur link is still not working.... If you wud have read my problem properly then u wud have understand y the solution is not working.

2nd to corinereyes

Thanks a lot buddy for spending valuable time on this. There is a basic problem in my code. There will not be any Sheet4 or something like that... So macro gives a run-time error whenever it doesnt find Sheet4.. thats y i want to make it dynamic... Can this problem be resolved?? pls help

3rd to bettatronic

I am checking out the threads... Still need to analyse how to incorporate the same into my macro... thanks a lot buddy.. anyways check out my post if u can help me on the same..

Thanks

crapmind

**corinereyes** · 08-13-2007, 02:32 AM

Hi Crapmind,

Try this code.

Private Sub CommandButton1_Click()

Set NewSheet = Sheets.Add(Type:=xlWorksheet)
NewSheet.Name = "Tol"
With NewSheet
Set vObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=345.75, Top:=11.25, _
Width:=165.75, Height:=40.5)
Set CmdBtn = vObj.Object 'This is a read only property
CmdBtn.Caption = "Misc Info"
End With

End Sub

i have put the code in a command button. See the example file i've attached.

Hope this helps you.

**royUK** · 08-13-2007, 04:05 AM

Originally Posted by crapmind

1st to royUK

ur link is still not working.... If you wud have read my problem properly then u wud have understand y the solution is not working.

crapmind

The link does work, and if you can't be bothered to explain why creating a hidden template sheet with the button required is not going to work then I won't waste any more time with your problem!

Adding a new sheet with command button

LinkBack

Thread Tools

Rate This Thread

Display

Adding a new sheet with command button

Adding a new sheet with a command button

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions