When I move my cursor to a cell can 1

[bnageshrao]

When I move my cursor to a cell can I display another cells value there as a default so that users dont have to type that value, at the same time if they dont need that value then they should be able to type a new value there. How do you write the VBA code for this?
Thanks in advance.

 

[DarkSun]

Not sure if this is what you want but try this.


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim MyCell As String
Dim MyOtherCell As String

MyCell = "$A$1"
MyOtherCell = "$B$1"

If Target.Address = MyCell And Target.Value = "" Then
Target.Value = ActiveSheet.Range(MyOtherCell).Value
End If

End Sub

 

[bnageshrao]

This works fine for one column, how do I extend to display default values for the entire column.I want to display the same default value whenever I move my cursor to any row and same column. Looks like I may have to loop it. If you know please let me know. Thanks.

 

[DarkSun]

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim MyColumn As String

MyColumn = 1

If Target.Column = MyColumn And Target.Value = "" Then
Target.Value = ActiveSheet.Cells(MyColumn, 1).Value
End If

End Sub

This code will input the value in A1 to any cell you go in on Column A.

Is this what you want?

 

[DarkSun]

Sorry the line with activesheet.cells(MyColumn, 1).value
I put the column and row round the wrong way.

It should be activesheet.cells(1, MyColumn).Value

 

[DarkSun]

You might want to replace Target.Value with ActiveSheet.Cells(Target.Row, Target.Column).Value in the IF statement. This stops an error occuring when you select an area.

 

[bnageshrao]

The code works very well, but I found another problem. That is I have a command button for which i have written the code to erase all the data which includes the above column. When I click the button I get the error which says Runtime error 13 Type mismatch. Is there any way to get around this.

 

[DarkSun]

Have you done the above change to the code. Replacing Target.Value with ActiveSheet.Cells(Target.Row, Target.Column).Value ??

 

[bnageshrao]

I did ActiveSheet.Cells(Target.Row, Target.Column).Value = ActiveSheet.Cells(4, 2).Value and still it gives me that error.

 

[DarkSun]

When you click on Debug which line does it take you to?