simple auto macro :)

[niceguy21]

Hello VBA People,


I am very new to VBA and have been tasked with a solution to a problem on an Excel dashboard we are attempting to build.
I believe our best bet is an Auto-Macro, and what a powerful tool these would appear to be!


Problem:


In a simple columnar range A1:A1000, we need to insure NO empty cells can be entered: any empty cell is filled with "BLANK".

We require an Auto-Macro that will detect cells in the range have been entered and it then fills the empty ones.

This is as far as my working got:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Intersect(Target, Sheets("raw data").Range("A1:A1000")) Is Nothing Then
For Each cell In Target
If cell.Value <> Empty Or cell.Value = 0 Then
cell.value="blank"
End If
Next cell
End If
End Sub


definately and most certainly wrong! but maybe it needs a few corrections?

Best

[GarysStudent]

How about:

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
For Each cell In Target
If cell.Value = "" Or cell.Value = 0 Then
cell.Value = "blank"
End If
Next cell
End If
End Sub
[/vba]

[niceguy21]

How about:

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Not Intersect(Target, Range("A1:A1000")) Is Nothing Then
For Each cell In Target
If cell.Value = "" Or cell.Value = 0 Then
cell.Value = "blank"
End If
Next cell
End If
End Sub
[/vba]

Yes this looks better!

However, when I put into ThisWorkbook in the VBE editor, then go back into one of the worksheets, change a cell in that range nothing happens..

is there a special way to execute this?

Thanks

[niceguy21]

Yes this looks better!

However, when I put into ThisWorkbook in the VBE editor, then go back into one of the worksheets, change a cell in that range nothing happens..

is there a special way to execute this?

Thanks

No worries I got it working now!!

Many, many thanks!

[GarysStudent]

The code will work at the worksheet level rather than the workbook level. First delete the code in the workbook code area and insert the code in the worksheet code area.

Becasue it is specific to a worksheet, it must be installed in each worksheet you want the function to be performed.

To install at the worksheet level, from the Excel window, right-click the tabname and select view code. Paste the material in there.