Excel vba question

  • Thread starter Thread starter levy.pate
  • Start date Start date
L

levy.pate

Is there any excel vba code that will accomplish the following:


I have data entered in cells A1 thru E1 lets say (Name, Address, Zip,
Country, and Phone). What I would like to be able to do is enter the
requested information directly in the cells that contain the info as
mentioned above. Then if I make a mistake or delete the info I typed in

all together the original data returns. Example, In cell B2 it shows
Address, so if I type in my address the word Address is replaced with
my acutal address however, if I deleted my address the word Address
would reappear. Hope someone out there understands this and is able to
provide a solution if there is one. Thanks in advance.
 
Yes.
Basically, you would use a Worksheet_Change event macro. You would write
code to produce an action only if the change is within some range or within
some columns or rows.
You would write the code to look for a change that produces a blank cell.
The code would then put the "original data" into that cell by issuing an
Undo command.
This would work with one cell at a time and wouldn't work at all if you were
to enter more than one entry in sequence before clearing the cell. For
instance, say that the word "Name" is the "original data". If you then
entered "Bill", then entered "Bob", then cleared the cell, you would not get
"Name" back. If that is what's going to happen, then the code would have to
be written so that it knows what the "original data" is. Post back with
more detail about what you need. HTH Otto
 
Otto,
The code I'm currently using will follow. Lets say I enter in cells A1
thru E1 the info I want someone to provide. Placing the cursor in A2
then pressing backspace or delete automaticlly puts the info from A1
into A2 then doing the same with the rest of the cells thru E2. Now A1
thru E1 and A2 thru E2 has the same info in them. I then hide row 1
because thats what drives the train and if the info in that row is
deleted the code dosen't work. The problem is if I put the cursor in
lets say cell C29 then press backspace or delete it puts the info from
row C1 into C29 etc. I would like to contain it to certain cells and
not effect other cells below them. My sheet looks something like this.

A B C D E
1 Name Address City State Zip
(this row is hidden)
2 Name Address City State Zip
(this is the row I want the info entered into)

With the code below I can place my cursor into A2 and type my name no
problem. If I delete my name the word Name would reappear. But like I
said above if I place the cursor in A3 and press backspace or delete
the word Name appears there as well. Hope this helps you in helping me.
Thanks for your interest and response. Ed

Private Const ColToStartThisBehaviourAt As Integer = 1
Private Const ColToStopThisBehaviourAt As Integer = 5


Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range

Application.EnableEvents = False
For Each c In Target.Cells
With c
If .Column >= ColToStartThisBehaviourAt And _
.Column <= ColToStopThisBehaviourAt Then
If .Row > 1 Then
If Len(.Value) = 0 Then
.Value = Me.Cells(1, .Column)
End If
End If
End If
End With
Next c
Application.EnableEvents = True
End Sub
 
Ed
If I'm following you correctly, you don't want your code to do anything if the cell above the "c" cell is empty. Is that correct?
Given a "Yes" to that question, put something like the following in your code:
If IsEmpty(.Offset(-1)) Then Exit Sub
I inserted that line in your code below where I think it should go. Post back if I didn't understand you correctly. HTH Otto
Otto,
The code I'm currently using will follow. Lets say I enter in cells A1
thru E1 the info I want someone to provide. Placing the cursor in A2
then pressing backspace or delete automaticlly puts the info from A1
into A2 then doing the same with the rest of the cells thru E2. Now A1
thru E1 and A2 thru E2 has the same info in them. I then hide row 1
because thats what drives the train and if the info in that row is
deleted the code dosen't work. The problem is if I put the cursor in
lets say cell C29 then press backspace or delete it puts the info from
row C1 into C29 etc. I would like to contain it to certain cells and
not effect other cells below them. My sheet looks something like this.

A B C D E
1 Name Address City State Zip
(this row is hidden)
2 Name Address City State Zip
(this is the row I want the info entered into)

With the code below I can place my cursor into A2 and type my name no
problem. If I delete my name the word Name would reappear. But like I
said above if I place the cursor in A3 and press backspace or delete
the word Name appears there as well. Hope this helps you in helping me.
Thanks for your interest and response. Ed

Private Const ColToStartThisBehaviourAt As Integer = 1
Private Const ColToStopThisBehaviourAt As Integer = 5


Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range

Application.EnableEvents = False
For Each c In Target.Cells
With c
If .Column >= ColToStartThisBehaviourAt And _
.Column <= ColToStopThisBehaviourAt Then
If .Row > 1 Then
Click to expand...
If IsEmpty(.Offset(-1)) Then Exit Sub 'New line of code
 
Back
Top