Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Data Validation over 2 workbooks?
I saw the below code in a post and works in one workbook perfectly but I need
it to work over two workbooks so i can update one workbook (C:/Source/Data.xls) each month by replacing data in it only. C:/Product/Sales.xls is where the user chooses the products sold from combo boxes where the data is derived from C:/Source/Data.xls I require the workbooks seperate for business reasons. How can I manipulate this code to operate over two workbooks please or different code to suit this purpose? I got no idea with scripts and Help was not helpful in Excel VB. Be much appreciated. Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim str As String Dim cboTemp As OLEObject Dim ws As Worksheet Dim wsList As Worksheet Set ws = ActiveSheet Set wsList = Sheets("ValueData") Cancel = True Set cboTemp = ws.OLEObjects("Products") On Error Resume Next With cboTemp 'clear and hide the combo box .ListFillRange = "" .LinkedCell = "" .Visible = False End With On Error GoTo errHandler If Target.Validation.Type = 3 Then 'if the cell contains a data validation list Application.EnableEvents = False 'get the data validation formula str = Target.Validation.Formula1 str = Right(str, Len(str) - 1) With cboTemp 'show the combobox with the list .Visible = True .Left = Target.Left .Top = Target.Top .Width = Target.Width + 5 .Height = Target.Height + 5 .ListFillRange = str .LinkedCell = Target.Address End With cboTemp.Activate End If errHandler: Application.EnableEvents = True Exit Sub End Sub '========================================= Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim str As String Dim cboTemp As OLEObject Dim ws As Worksheet Set ws = ActiveSheet Application.EnableEvents = False Application.ScreenUpdating = True Set cboTemp = ws.OLEObjects("Products") On Error Resume Next With cboTemp .Top = 10 .Left = 10 .Width = 0 .ListFillRange = "" .LinkedCell = "" .Visible = False .Value = "" End With errHandler: Application.EnableEvents = True Exit Sub End Sub '==================================== 'Optional code to move to next cell if Tab or Enter are pressed 'from code by Ted Lanham Private Sub Products_KeyDown(ByVal _ KeyCode As MSForms.ReturnInteger, _ ByVal Shift As Integer) Select Case KeyCode Case 9 'Tab ActiveCell.Offset(0, 1).Activate Case 13 'Enter ActiveCell.Offset(1, 0).Activate Case Else 'do nothing End Select End Sub '==================================== |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data validation with validation lists and combo boxs | Excel Discussion (Misc queries) | |||
Should Merging workbooks pick up new data or only edited data? | Excel Worksheet Functions |