Solved: Addins - Using worksheet as a template

[samohtwerdna]

I hate to bud in - but I have a question for killian on Excel Addins.

I want to create a worksheet that is formated and ready to print along with my Addin (user form) when the "Print Quote" button is pushed the worksheet is up dated and made active.

I was creating the sheet from code - but had a bit of formatting that took to long - So can I call my Sheet1 from my .xla from the current workbook??

Here is what I have:
[VBA]
Private Sub cmdPrint_Click()
Dim NewSheet As Worksheet, Cadsht As Worksheet

Set Cadsht = Worksheets(1)
Cadsht.Name = Worksheets(1).Name

'this is where I want to call my worksheet template
Set NewSheet = Worksheets(2)
NewSheet.Name = "Price Quote"

Range("a1").Select

With ActiveCell

.Offset(3, 1).Value = Cadsht.Name
.Offset(4, 0).Value = "Finish:"
.Offset(4, 1).Value = cboFinish.Value
.Offset(4, 2).Value = txtFinish.Value

.Offset(7, 0).Value = "Total Surface Area"
.Offset(7, 4).Value = lblTotl.Caption
.Offset(8, 0).Value = "Total Interior Area"
.Offset(8, 4).Value = lblTotl2.Caption

.Offset(10, 0).Value = "Grand Total List Price"
.Offset(10, 4).Value = txtQuoteTotal.Value
End With


End Sub[/VBA]

[mvidas]

Hi again samohtwerdna,

Just to let you know, I split this post from the original thread as it is unrelated to that topic.

Are you asking if you could use worksheet 1 of the addin as your template? You sure can, I use that idea for a couple addins that I have created. Just specify "ThisWorkbook" before .worksheets(1) to refer to the addin's sheet, and "ActiveWorkbook" to refer to the non-addin workbook. Using the following, a price quote sheet will be added to the active workbook:[vba]Private Sub cmdPrint_Click()
Dim NewSheet As Worksheet, Cadsht As Worksheet

Set Cadsht = Worksheets(1)

'this is where I want to call my worksheet template
ThisWorkbook.Worksheets(1).Copy Before:=ActiveWorkbook.Sheets(1)
Set NewSheet = ActiveWorkbook.Worksheets(1)
NewSheet.Name = "Price Quote"

With NewSheet
.Range("B4").Value = Cadsht.Name
.Range("A5").Value = "Finish:"
.Range("B5").Value = cboFinish.Value
.Range("C5").Value = txtFinish.Value

.Range("A8").Value = "Total Surface Area"
.Range("E8").Value = lblTotl.Caption
.Range("A9").Value = "Total Interior Area"
.Range("E9").Value = lblTotl2.Caption

.Range("A11").Value = "Grand Total List Price"
.Range("E11").Value = txtQuoteTotal.Value
End With
End Sub[/vba]Also, though it makes little difference, a shorter way of writing your With block would be to use:[vba] With NewSheet
.Range("B4").Value = Cadsht.Name
.Range("A5:C5") = Array("Finish:", cboFinish.Value, txtFinish.Value)
.Range("A8:A9") = Application.Transpose(Array("Total Surface Area", _
"Total Interior Area"))
.Range("E8:E9") = Application.Transpose(Array(lblTotl.Caption, lblTotl2.Caption))
.Range("A11").Value = "Grand Total List Price"
.Range("E11").Value = txtQuoteTotal.Value
End With[/vba]Matt

[Killian]

In terms of worksheet content, an AddIn is no different to a workbook, but it's worksheets are hidden.
To create a new workbook with a formatted sheet, I would imagine the easiest thing to do would be to copy a pre-formatted (hidden) sheet from the addin - using Sheet.Copy with out a paramater will create a new workbook for the sheet. it will become the activesheet so you can set a reference to that.[VBA]Dim newsheet As Worksheet

'code run from the AddIn means ThisWorkbook is the AddIn
'Sheets(1) is your prefomatted quote
ThisWorkbook.Sheets(1).Copy
Set newsheet = ActiveSheet[/VBA]

[samohtwerdna]

OK, the code works great!

I was not sure if ThisWorkbook referred to the Addin Workbook or the active Workbook.
Thanks Matt for the tip on simplifying my "With" statement - I always need help simplifying!

Thanks Matt and Killian!


Andrew T. Adcock

[mvidas]

Glad to help!
And HA! I assumed your username here was your actual name, not first/mid backwards. Never even tried looking at it backwards. Let us know if you need anything else!