Excel; Insert cells with function!

[Ammarbokhari]

Hi,
I need your help yet again.
What I am trying to do is, I need to make a data validation list based on the values on another sheet. I know that I can give the name to list on another sheet to use it in data validation in current sheet. But the I have more than 500 values in data validation list, so I needed a macro to do auto-fill. To do this, I added a function which creates a combo box in current cell if its double clicked and then combo box auto-fill is used to fill the data and once I press enter the combo box is hidden and the value is shown in the cell.
There comes the part I need your help on, once the combo box is activated I need data validation list on the same sheet otherwise the auto-fill function seems not to work (I don't know why its not working, if it should. It works fine when data is on the same sheet). If auto-fill can be fixed that will be the best solution possible. Otherwise what I require is, when I add a row in the source sheet from where I have linked my current sheet's data validation link, the a cell should be added in current sheet to accommodate the new entry of data for validation list. Otherwise the list in current sheet will be in complete once rows of data are added in source sheet.
Hope I have made my question clear enough.
Thank you for your time.

 

[GlennUK]

Can you explain what you mean by: "But the I have more than 500 values in data validation list, so I needed a macro to do auto-fill."?

 

[Ammarbokhari]

Sorry a typographic mistake
I have 500+ values in my list, so if these are selected using mouse (which normally is the case with data validation list, unless the same value is being selected for more than one times) it can take quite some time. Therefore auto-fill (start typing and program suggests you related entries) was required, for which I am using a macro.
Hope that explains

 

[Ammarbokhari]

I am awaiting a response.
Is there a way by which I can increase the number of formula filled cells i.e

Sheet1!A1=Sheet2!A1
Sheet1!A2=Sheet2!A2
Sheet1!A3=Sheet2!A3
Sheet1!A4=Sheet2!A4
and if a number of cells are added in Sheet1 which can be counted using Count same number of cells to be added on relative position in Sheet2 and formula inserted, and hence all the data on both Sheets remains the same!
I want one input to be displayed on both sheets with some sort of function.
I require only one column of Sheet1 to be displayed on Sheet2.
Thank you

 

[GlennUK]

I am awaiting a response.

Show your code .... it sounds like what you are aiming for should work, so a look at your code is required next, I'd say.

As for your latest query, why don't you just fill formulas down as far as data will ever be used?

 

[Ammarbokhari]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("JOBNAME")
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 = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

This is the code I am using.
And this is made by someone else, as I am ignorant about VBA syntax.

And at the other solution that you gave;
"As for your latest query, why don't you just fill formulas down as far as data will ever be used?"

The job names keep increasing and we have very small duration jobs Ranging from a couple of days to a couple of months maximum, hence every month a lot of new jobs are started. So if I use the formula for complete length of the page, that "will not look good". (This sheet is to be used by various people so I want chances of human errors to be reduced as much as possible)

 

[GlennUK]

You could change:

.ListFillRange = ws.Range(str).Address

to

.ListFillRange = "sheet_with_list!name_of_list"

And, saying that pre-loaded formulas "will not look good", doesn't mean much unless you say what it is about the look of it that you don't like. I'll guess that it's maybe that zeroes show when referencing an empty cell ... you could either put an IF function around your formula to suppress the behaviour, or format the area to suppress errant zeroes with a custom number format, like
0;-0;;@

 

[Ammarbokhari]

You could change:

.ListFillRange = ws.Range(str).Address

to

.ListFillRange = "sheet_with_list!name_of_list"

This code did not yield the required result as the list appeared empty.
I have uploaded the file to 4shared.com, if you can please solve the issue.
http://www.4shared.com/file/AYRr8wwM/Sample.html
Thank you in advance.

 

[Ammarbokhari]

The previous link may appear broken
Please use this:
http://www.4shared.com/file/yYuoNGfb/Sample.html
Sorry for inconvenience
Thank you

 

[GlennUK]

I cannot navigate such links while I am at work, due to corporate security policies. Can you post your amended code?