Dynamic Range - VBA control of embedded Excel in powerpoint

Joined
Nov 1, 2013
Messages
1
Reaction score
0
Hi, First time user here so apologies if i'm off the mark with my first question!

I have a simple piece of code that i have adopted and used in Excel. This code ensures that a link is updated and then dynamically sets a range for validation on cells.

This works really well for me. What i want to do now is replecate this on an embedded excel sheet within powerpoint...

I am used to VBA in Excel (although sometimes take the long route to an answer) and stumbled upon the VBA editor available by clicking on the embedded Excel, I just can't seem to get any code i add in there to "stick" once saved, closed and re-opened. :(

As an FYI my code is below (and currently works on worksheet_open)... i am using office 2010:

Private Sub Workbook_Open()

Dim SelectSheet As Worksheet
Dim DataSheet As Worksheet
Dim rLastCell As Range
Dim rng As Range
Dim PickRange As Range

ActiveWorkbook.UpdateLink Name:="SourceTemplate1.xls", Type:=xlExcelLinks
Set SelectSheet = Worksheets("LookUps")
Set DataSheet = Worksheets("Template")

SelectSheet.Activate
Set rLastCell = SelectSheet.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)

Set PickRange = SelectSheet.Range(Cells(1, 1), rLastCell)

DataSheet.Activate

Set rng = DataSheet.Range("B5:C22")
With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & "LookUps!" & PickRange.Address
.IgnoreBlank = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top