Automated entry (based on the appearance of one cell) - Possible?

[Doug Glick]

OK - I'm sure this has to be possible but nothing I try works.
Assume the following:
There is a long string of repeating numbers (it is a work schedule).
Sometimes it stays blank (when workers don't come in) and other times
when they do the numbers are all entered. The thing is they are the
same, so I'm asking if it is possible for them to appear as soon as I
enter the first one.
A simplified scenario would be like this:
As soon as I enter '4' in A1.
'5' would appear in A2.
'8' would appear in A3.
'3' would appear in A4.
The numbers are not mathematically related to each other but they are
always the same numbers in the same locations.
Is this possible?
Please help - I'm getting tired of typing them all in each time!
Thanks, Doug

 

[Bernard Rey]

A simplified scenario would be like this:

As soon as I enter '4' in A1.
'5' would appear in A2.
'8' would appear in A3.
'3' would appear in A4.
The numbers are not mathematically related to each other
but they are always the same numbers in the same locations
Is this possible?

One way to do so, based upon a simple double click in the
first cell (in your example A1):

Control-click on the sheet's tab to access the related
Code Sheet. This will open the VB Editor.

Paste the lines hereunder on that sheet.

----------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Excel.Range, Cancel As Boolean)

Set isect = Application.Intersect(Target, Range("A:A"))
If isect Is Nothing Then
Exit Sub
Else
MyRow = Target.Row
Range("A" & MyRow).Value = 4
Range("B" & MyRow).Value = 5
Range("C" & MyRow).Value = 8
Range("D" & MyRow).Value = 3
Cancel = True
End If
End Sub
------------------------------------------------------

Close the VB Editor. Now, a simple double-clic in any cell
in column "A" should fill the four columns as indicated.

Note that you won't be able to edit a cell in col "A" any
more in that sheet by double-clicking on it. But I think
it's not much of a problem, as you can stil edit it some
other way.

If you think it doesn't match your needs, just post back,
we'll see if there's some other (better) way...

 

[Doug Glick]

No this would work perfectly (even better than I thought possible as
it wouldn't entail having to deal with the already entered rows).
What I don't understand though is where are the variables that I'll
have to change to adjust it to the actual worksheet I'm dealing with
here.
Thanks again, Doug

 

[Doug Glick]

Hi - Ok I figured out what I had to change and it is working.
Beautiful!
One question though. What if in the future the numbers have to change?
So for example let's say this work perfectly for rows 100-3000, but
then I need to change the numbers that will get automatically entered.
If I reopen the code and change the numbers will it go back and change
all the previous entries?
If it does that -- and I can't limit each bit of code to particular
rows -- then this unfortunately won't work for me.
Thanks, Doug

 

[JE McGimpsey]

Hi - Ok I figured out what I had to change and it is working.
Beautiful!
One question though. What if in the future the numbers have to change?
So for example let's say this work perfectly for rows 100-3000, but
then I need to change the numbers that will get automatically entered.
If I reopen the code and change the numbers will it go back and change
all the previous entries?
If it does that -- and I can't limit each bit of code to particular
rows -- then this unfortunately won't work for me.

As written, Bernard's code won't go back and change the codes unless you
go back and doubleclick an entry in a previous row.

To be able to change the code, I'd recommend putting your code
information on a second sheet, so on a sheet named "Codes", for example,
you could put

A1: Codes
A2: 4
A3: 5
A4: 8
A5: 3

You can then hide the sheet (Format/Sheets/Hide) if you wish.

The code could then reference that range (note that I modified Bernard's
macro to position the range vertically, as in your original post):

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
With Target
If .Column = 1 Then
.Resize(4, 1).Value = Sheets("Codes").Range("A2:A5").Value
Cancel = True
End If
End With
End Sub

If you wanted to prevent overwriting previous rows (i.e., if B100
already has a value, don't change the code in B100:B103), add this
modification:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
With Target
If .Column = 1 Then
If IsEmpty(.Value) Then
.Resize(4, 1).Value = _
Sheets("Codes").Range("A2:A5").Value
Cancel = True
End If
End If
End With
End Sub

Now, whenever the codes change, unhide the Codes sheet and change them.

 

[Bernard Rey]

Doug Glick wrote :

One question though. What if in the future the numbers have to change?
So for example let's say this work perfectly for rows 100-3000, but
then I need to change the numbers that will get automatically entered.
If I reopen the code and change the numbers will it go back and change
all the previous entries?
If it does that -- and I can't limit each bit of code to particular
rows -- then this unfortunately won't work for me.

Sorry, it's not too clear what you would like to see

If you mean : "Will the values that have already been entered be
automatically altered when I change the values in the macro?" the answer is
"NO!" The macro enters fixed values in the cells, and they won't be modified
automatically. If you want to modify the values in a row that has already
been filled, simply double-clic cell Axxx, and the new values will replace
the previous ones.

If you mean : "If I want to keep on entering the same values in the rows
from 100 to 3000, but I'd also like to enter some different values in the
rows (let's say) 3001 to 5000, can it still do what I'd want?" the answer is
"YES" but the macro has to be modified then, in order to perform two
different operations according to the row in which you click. So you'll have
to replace the macro with these new lines:


-------------------------------------------------------------------
Private Sub Worksheet_BeforeDoubleClick(ByVal Target _
As Excel.Range, Cancel As Boolean)

Set isect = Application.Intersect(Target, Range("A100:A5000"))

If isect Is Nothing Then
Exit Sub
Else
MyRow = Target.Row
If MyRow < 3001 Then ' when row is between 100 and 3000
Range("A" & MyRow).Value = 4
Range("B" & MyRow).Value = 5
Range("C" & MyRow).Value = 8
Range("D" & MyRow).Value = 3
Cancel = True
Else ' when row is between 3001 and 5000
Range("A" & MyRow).Value = 6 ' new values (to be adapted)
Range("B" & MyRow).Value = 7 ' "" ""
Range("C" & MyRow).Value = 10 ' "" ""
Range("D" & MyRow).Value = 5 ' "" ""
Cancel = True
End If
End If
End Sub
--------------------------------------------------------------------

Now this might have to be done a bit differently if you have some more
requirements, of course. Ie. if you have to add many different sequences,
there are different possibilities to be dealt with...

 

[Doug Glick]

Ok that worked well!
To be sure this suits me in the long run though I have one remaining
question.
Let's assume this works fine for a few months (i.e. rows 100-1200).
But then if I have to change the numbers that are assigned to the
cells, can I just go in and edit the script at say row 1201 and change
the numbers to th new ones?
The issue will be: Will that change the numbers in the previous rows
(100-1200) to the new numbers?
Ideally I'd like to be able to have:
Double click on A100 and have all the numbers fill in across (this
works).
Eventually edit the script (say at cell A1201) to change some/all of
the numbers.
When I double click on A1201 it fills in across (and down for any
following rows) with the newly edited numbers, but doesn't touch the
ones that had already been entered in rows 100-1200.

Will that work?
Thanks, Doug

 

[Bernard Rey]

Doug Glick wrote :

Ok that worked well!
To be sure this suits me in the long run though I have one remaining
question.
Let's assume this works fine for a few months (i.e. rows 100-1200).
But then if I have to change the numbers that are assigned to the
cells, can I just go in and edit the script at say row 1201 and change
the numbers to th new ones?
Yes.

The issue will be: Will that change the numbers in the previous rows
(100-1200) to the new numbers?

No. Not automatically.

Ideally I'd like to be able to have:
Double click on A100 and have all the numbers fill in across (this
works).
Eventually edit the script (say at cell A1201) to change some/all of
the numbers.
When I double click on A1201 it fills in across (and down for any
following rows) with the newly edited numbers, but doesn't touch the
ones that had already been entered in rows 100-1200.

Will that work?

As described in previous posts, both from JE McGimpsey and me, the answer to
the question is yes. This is exactly how the (first) script works. I guess
you can test it just the way you describe. Or have a look at the different
solutions in the posts to find some alternative ways to change things if you
prefer these other possibilities, or even try some new combination (Visual
Basic requires a lot of trials and modification).

 

[Doug Glick]

Ok I have a simple final question.
Based on the methods described above, I have created a working 'double
click' in (say) column 'A' of my data. I also see how it isn't too big
a deal if I need to change it every once in a while. And, as you both
said correctly, the change will only influence the row at which the
change in VBE is made (and all future rows).
So the final question: Can I create two of these in the same row?
That is, can i have it so that a double click in column 'A' fills in
columns B, C and D with fixed numbers and then a double click in
column 'G' fills in columns H, I and J?
So far when I write the second one it seems to override the first one
and fill in for it.
Thanks, Doug

 

[JE McGimpsey]

Ok I have a simple final question.
Based on the methods described above, I have created a working 'double
click' in (say) column 'A' of my data. I also see how it isn't too big
a deal if I need to change it every once in a while. And, as you both
said correctly, the change will only influence the row at which the
change in VBE is made (and all future rows).
So the final question: Can I create two of these in the same row?
That is, can i have it so that a double click in column 'A' fills in
columns B, C and D with fixed numbers and then a double click in
column 'G' fills in columns H, I and J?
So far when I write the second one it seems to override the first one
and fill in for it.

one way:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, _
Cancel As Boolean)
Dim vA_Values As Variant
Dim vG_Values As Variant
vA_Values = Array(1, 2, 3)
vG_Values = Array(4, 5, 6)
If Not Intersect(Target, Range("A:A,G:G")) Is Nothing Then
With Target
.Offset(0, 1).Resize(1, 3).Value = _
IIf(.Column = 1, vA_Values, vG_Values)
End With
Cancel = True
End If
End Sub

 

[Doug Glick]

OK I went with your solution but tried to adjust it to the actual
case. One problem I didn't report was that I had a discontinuous set
of variables (i.e. double click fill is that row, say A, and then
fills in B-D and F). I solved this by just putting '0s' in for now --
though if there is a way to skip them that would be better. I tried
just putting empty commas (, , , , ) and that didn't work.
What I ended up with looked like this:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, _
Cancel As Boolean)
Dim vG_Values As Variant
Dim vQ_Values As Variant
vG_Values = Array(3.6, 20, 0, 0, 0, 0, 0, "10.10.10", 80, 31)
vQ_Values = Array(60, 20, 60, 20, 40, 20, 40, 20, 60, 20, 60,
20, 60, 20, 60, 20, 60, 10, 60, 20, 60, 20, 60, 20, 60, 20, 60, 20)
If Not Intersect(Target, Range("G:G,Q:Q")) Is Nothing Then
With Target
.Offset(0, 1).Resize(1, 3).Value = _
IIf(.Column = 1, vA_Values, vG_Values)
End With
Cancel = True
End If
End Sub

Problem is I got an error saying that 'only comments may appear after
end sub, end function or end property and it had highlighted in yellow
the whole first section, ending with 'Cancel As Boolean').
Any idea what I did wrong?
Thanks, Doug

 

[JE McGimpsey]

Problem is I got an error saying that 'only comments may appear after
end sub, end function or end property and it had highlighted in yellow
the whole first section, ending with 'Cancel As Boolean').
Any idea what I did wrong?

Either through editing, or through pasting in the routine from a web
interface, you have some extraneous non-printing characters in your
code. For small routines, one way to correct this is to highlight the
white space before the text in each physical line, delete it, and insert
tabs or spaces to indent the line again. Another way is to copy the text
into a text editor (like TextEdit), delete everything in the module,
then copy back from the text editor. Note: make sure the text editor is
in plain text mode.

Instead of 0's you can use null strings to leave cells blank. This will,
however, overwrite anything in those columns. To actually skip the cells
will require a different technique, such as looping through the array.

Note that since your arrays are different sizes, you need to add some
code to determine the size before assigning the values:

Try:

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, _
Cancel As Boolean)
Dim vG_Values As Variant
Dim vQ_Values As Variant
vG_Values = Array(3.6, 20, "", "", "", "", _
"", "10.10.10", 80, 31)
vQ_Values = Array(60, 20, 60, 20, 40, 20, 40, _
20, 60, 20, 60, 20, 60, 20, _
60, 20, 60, 10, 60, 20, 60, _
20, 60, 20, 60, 20, 60, 20)
If Not Intersect(Target, Range("G:G,Q:Q")) Is Nothing Then
With Target
.Offset(0, 1).Resize(1, UBound(IIf(.Column = 7, _
vG_Values, vQ_Values)) + 1).Value = _
IIf(.Column = 7, vG_Values, vQ_Values)
End With
Cancel = True
End If
End Sub

 

[Doug Glick]

Thanks that fixed it - I must have been copying in unintended
material.
One last problem remains -- I'm afraid in that first array, there is a
cell that has a formula in it. Using what you have given me, as you
said, I'm losing it on every row as it is being set to " ".
So what does looping an array mean? Is it something I can just for the
first set of data linked to the double click in column 'G'. If so, it
is column 'M' that I'd like to leave blank (i.e. be skipped over).
That is the one right before the "10.10.10" column.
Hopefully this doesn't mean the rest of the solution won't work
anymore as things are perfect now except for this one remaining issue.
Thanks, Doug

 

[JE McGimpsey]

Thanks that fixed it - I must have been copying in unintended
material.
One last problem remains -- I'm afraid in that first array, there is a
cell that has a formula in it. Using what you have given me, as you
said, I'm losing it on every row as it is being set to " ".
So what does looping an array mean? Is it something I can just for the
first set of data linked to the double click in column 'G'. If so, it
is column 'M' that I'd like to leave blank (i.e. be skipped over).
That is the one right before the "10.10.10" column.
Hopefully this doesn't mean the rest of the solution won't work
anymore as things are perfect now except for this one remaining issue.

By Looping through the array I meant putting some sort of marker in the
array to indicate a skip, e.g.

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, _
Cancel As Boolean)
Dim vG_Values As Variant
Dim vQ_Values As Variant
Dim i As Long
vG_Values = Array(3.6, 20, "", "", "", "", _
"skip", "10.10.10", 80, 31)
vQ_Values = Array(60, 20, 60, 20, 40, 20, 40, _
20, 60, 20, 60, 20, 60, 20, _
60, 20, 60, 10, 60, 20, 60, _
20, 60, 20, 60, 20, 60, 20)
If Not Intersect(Target, Range("G:G,Q:Q")) Is Nothing Then
With Target
If .Column = 7 Then
For i = 0 To UBound(vG_Values)
If vG_Values(i) <> "skip" Then _
.Offset(0, i).Value = vG_Values(i)
Next i
Else
.Resize(1, UBound(vQ_Values) + 1).Value = vQ_Values
End If
End With
Cancel = True
End If
End Sub

 

[Doug Glick]

One small problem still remains with this solution.
In the column where 'skip' is being put it is overriding the formula
that is supposed to be there (i.e. carried over from previous rows).
That formula will need some of the numbers that are being put in by
the double click, but the key would be can the double-click simply
leave that column alone and fill in around it, such that the formula
could ride through?
Thanks, Doug

 

[JE McGimpsey]

One small problem still remains with this solution.
In the column where 'skip' is being put it is overriding the formula
that is supposed to be there (i.e. carried over from previous rows).
That formula will need some of the numbers that are being put in by
the double click, but the key would be can the double-click simply
leave that column alone and fill in around it, such that the formula
could ride through?

Not sure what you mean by "overriding the formula". Using the code I
gave you, any column with a corresponding "skip" in the vG_Values or
vQ_Values array is left untouched.

 

[Doug Glick]

No actually it is printing the word 'skip' in the cell.

 

[JE McGimpsey]

No actually it is printing the word 'skip' in the cell.

As written, I can't get the code to fail that way. Make sure

1) there are no extraneous characters in vG_Values, and

2a) You use lowercase "skip" in vG_Values, or

2b) You replace

If vG_Values(i) <> "skip" Then _

with

If LCase(vG_Values(i)) <> "skip" Then _

 

[Doug Glick]

Well I don't see anything ... Do you see a mistake in the code I'm using? :

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, _
Cancel As Boolean)
Dim vG_Values As Variant
Dim vQ_Values As Variant
vG_Values = Array(3.6, 20, "", "", "", "", _
"skip", "10.10.10", 80, 31)
vQ_Values = Array(60, 20, 60, 20, 40, 20, 40, _
20, 60, 20, 60, 20, 60, 20, _
60, 20, 60, 10, 60, 20, 60, _
20, 60, 20, 60, 20, 60, 20)
If Not Intersect(Target, Range("G:G,Q:Q")) Is Nothing Then
With Target
.Offset(0, 0).Resize(1, UBound(IIf(.Column = 7, _
vG_Values, vQ_Values)) + 1).Value = _
IIf(.Column = 7, vG_Values, vQ_Values)
End With
Cancel = True
End If
End Sub