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
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