PDA

View Full Version : Exporting a sheet with macros included



joshcas
05-29-2013, 12:21 PM
Hi,
I’m wondering how can I export an excel spreadsheet with themacros included as well, the tricky part is that the sheet contains images witha macro hyperlink on some of them so normally if I export it, will preserve the hyperlink for the macro tothe old workbook .
Any help will be much appreciated


10071

SamT
05-29-2013, 04:36 PM
That is not an easy question to answer. Her's what I think you have and what I think you want.

You have a Workbook, Start.xlsm with a bunch of macros. The macros are stored in the ThisWorkbook code page, or in a standard code module.

You have another workbookwith some other sheets in it that you want to put a copy of one of Start.xlsm's Worksheets into.

You want some or all of the macros in Start.xlsm to also be placed in the other book.

You also want the "new" sheet to refer to the "new" macros.

The simple and easy solution depends on if you can use the Sheets' Code page to store the macros it needs. Then you just copy the sheet to the new book and it's macros go with it.

joshcas
05-31-2013, 07:22 AM
Hi Sam, Thank you for answering, yeah I know it was a tricky question but I decided to take your approach. So basically I created a process to copy and rename the workbook from (Start.xlsm) to (US File.xlsm) but the file will behave different depending on the file name, here is a little example of how it behaves

Private Sub Workbook_Open()

start

End Sub


Function start()
Application.Cursor = xlWait

'blackout ' Not needed anymore
'button_blackout ' Not needed anymore
If ActiveWorkbook.Name = "Start.xlsm" Then
Sheets("Start").Visible = True
Sheets("Start").Select
For Each Sh In Worksheets
If Not Sh.Name = "Start" Then
Sh.Visible = True
End If
Next
Sheets("Timestamp").Visible = xlVeryHidden
'Disable the update icons on the start screen
With Sheets("Start")
.Shapes.Range(Array("Picture 30", "Picture 45", "Picture 59", "Picture 73", _
"Picture 100", "Textbox 94", "Textbox 95", "Picture 14", "Picture 27", "Group 12", _
"Rectangle 1")).Visible = False
.Shapes.Range(Array("Picture 15", "Picture 5", "Group 7", "Group 126", "Group 152", "Group 102", "Group 146")).Visible = True
.Shapes("Rectangle 25").Fill.ForeColor.RGB = RGB(255, 255, 255)
End With

Call StartScreen_Initialize
Else
If ActiveWorkbook.Name = "US File.xlsm" Then
Sheets("US").Visible = True
Sheets("US").Select
For Each Sh In Worksheets
If Not Sh.Name = "US" Then
Sh.Visible = True
End If
Next
Sheets("Start").Visible = xlVeryHidden
Sheets("Timestamp").Visible = xlVeryHidden
End If

End If


Application.Cursor = xlDefault
End Function

SamT
05-31-2013, 10:45 AM
JoshCas,

I think your program logic is off. It looks like If Workbook is "Start" then hide Sheet "Start" and all its Shapes. And, If Workbook is "US" then Hide Sheet "US."

I think you want the opposite, This first code is what I think you want your code logic to be.

In all cases in my code, "Me" = "ThisWorkbook." "Start" is a keyword, so I changed the function to "startX." It doesn't (usually) apply to sheet names.

If ActiveWorkbook.Name = "Start.xlsm" Then
For Each Sh In Worksheets
If Not Sh.Name = "US" Then
Sh.Visible = True
End If
Sheets("US").Visible = xlVeryHidden
'Disable the update icons on the start screen
'Removed for clarity
Else
If ActiveWorkbook.Name = "US File.xlsm" Then
For Each Sh In Worksheets
If Not Sh.Name = "Start" Then
Sh.Visible = True
End If
Sheets("Start").Visible = xlVeryHidden
End If
End If
Sheets("Timestamp").Visible = xlVeryHidden

This code is built on your code, but with reversed logic. This code is designed to make it easier to update if, in the future, you have to add still another local sheet to your system.
Option Explicit

Private Sub Workbook_Open()

Dim LocalSht As String
If Me.Name = "Start" Then LocalSht = "Start"
If Me.Name = "US File" Then LocalSht = "US"

startX LocalSht

End Sub


Function startX(LocalSht As String)

Dim VeryHiddenShts As Variant
VeryHiddenShts = Array("US", "Start", "TimeStamp")
Dim WkSht As Worksheet
Dim i As Integer

Application.ScreenUpdating = False
Application.Cursor = xlWait

'Set all worksheets visible
For Each WkSht In Me.Worksheets
WkSht.Visible = True
Next WkSht

'Don't hide LocalSht
For i = LBound(VeryHiddenShts) To UBound(VeryHiddenShts)
If Not VeryHiddenShts(i) = LocalSht Then _
Me.Worksheets(VeryHiddenShts(i)).Visible = xlVeryHidden
Next i

'I think this is redundant if "Start" is hidden, But you may have opther things
'going on that I'm not aware of.
'If "Start" is Visible, then, enable the update icons on the start screen,
'Else, Hide them.
With Worksheets("Start")
.Shapes.Range(Array("Picture 30", "Picture 45", "Picture 59", "Picture 73", _
"Picture 100", "Textbox 94", "Textbox 95", "Picture 14", "Picture 27", "Group 12", _
"Rectangle 1")).Visible = Sheets("Start").Visible

.Shapes.Range(Array("Picture 15", "Picture 5", "Group 7", "Group 126", _
"Group 152", "Group 102", "Group 146")).Visible = Sheets("Start").Visible

.Shapes("Rectangle 25").Fill.ForeColor.RGB = RGB(255, 255, 255)
End With

Sheets(LocalSht).Select
'If LocalSht = "Start" Then _???' I don't know "StartScreen"
StartScreen_Initialize

Application.Cursor = xlDefault
Application.ScreenUpdating = True
End Function

I took a good look at your Shapes hiding code and I think you can substitute this bit for what is above.
'I think this is redundant if "Start" is hidden, But you may have other things
'going on that I'm not aware of.
'If LocalSht Has Controls and is Visible, then, enable the update icons on the
'start screen, Else, Disable them.

Dim Ctrl As OLEObject
On Error Resume Next
If Worksheets(LocalSht).OLEObjects.Count > 0 Then
For Each Ctrl In Worksheets(LocalSht).OLEObjects
Ctrl.Enabled = Worksheets(LocalSht).Visible
Next Ctrl
End If
Finally, If I truly understand what you are trying to accomplish, which is, that Sheet("US") is identical to Sheet("Start") except that it has no clickable shapes on it, so that you can have a workbook that doesn't use any encoded macros.

With this code there is no need for location specific sheets, itwill disable the "clickability" of the Shapeson Sheet("Start") if the Workbook name is not "Start." It assumes that "TimeStamp" is always xlVeryHidden
Option Explicit

Private Sub Workbook_Open()

Dim Toggle As Boolean

If Me.Name = "Start" Then Toggle = True
SetStartControls Toggle

End Sub

Private Sub SetStartControls(Toggle As Boolean)
Dim Ctrl As OLEObject

For Each Ctrl In Worksheets("start").OLEObjects
Ctrl.Enabled = Toggle
Next Ctrl

End Sub
Obviously, I can't test it, so If you have errors or just want to understand the code's logic, feel free to ask.

You noticed that I changed all references to "sh" and Sheets" to "WkSht' and "WorkSheets." There are three Sheets collections, WorkSheets, Charts, and Sheets. Charts is the collection of Chart Sheets; Worksheets, the collection of the non-Chart sheets; and Sheets, the collection of all the Items in both the other collections.

I believe that it is important to always be specific, therefore I always use ChSht, WkSht and Sht to refer to them.

joshcas
06-03-2013, 09:38 AM
Hey SamT, Thanks for you follow up on this matter, it’s my mistake because I wasn’t very clear at the beginning but let me give more background on this.

There are 2 files, one is going to be the regional file and the other the country file.

Start.xlsm is the regional file and is able to see all sheets of the workbook, start.xlsm contains a custom GUI to be able to display if the region already updated the country numbers.

US File.xlsm is the country file and it’s locked down on the number of things that you can do and see on the workbook, it also includes a custom UI, the file will be used only to update the numbers .

In essence Start and US File are the same file just that they behave different based on the file name.

That system is working great thanks to you approach and your code, so thank you so much, it was a brilliant idea.

I can’t post real screenshots because of the non-disclosure agreement but I can show screenshots of the first concepts to give you a better idea.




10085

SamT
06-04-2013, 06:54 AM
:thumb:beerchug: