johnreid7477
New Member
- Joined
- May 28, 2013
- Messages
- 29
Hi all,
I need help how changes this VBA code to link another workbooks, i'm don't have basic VBA i need tutorial.
Original workbooks : "TEST.xlsm"
I Need link this another workbooks : "PRICELISTUPDATE.xlsx" in Worksheets : "PRICELIST"
Thanks - John
I need help how changes this VBA code to link another workbooks, i'm don't have basic VBA i need tutorial.
Original workbooks : "TEST.xlsm"
I Need link this another workbooks : "PRICELISTUPDATE.xlsx" in Worksheets : "PRICELIST"
Rich (BB code):
Private Sub ComboBox1_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'Hide combo box and move to next cell on Enter and Tab
Select Case KeyCode
Case 9
ActiveCell.Offset(0, 1).Activate
Case 13
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsL As Worksheet
Set ws = ActiveSheet
Set wsL = Worksheets("PRICELIST")
On Error GoTo errHandler
If Target.Count > 1 Then GoTo exitHandler
Set cboTemp = ws.OLEObjects("ComboBox1")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
If Left(str, 8) = "INDIRECT" Then
str = Replace(Replace(str, "INDIRECT(", ""), ")", "")
str = ws.Range(str).Value
End If
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If
exitHandler:
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
errHandler:
Resume exitHandler
End Sub
Thanks - John