Running a Macro continuously

[Arcadian]

Hello everyone,

I have a question.

I want to create a simple macro which continuously checks the data in cell A1.
Right now it doesn't work. I have to select "Run Macro" in the menu everytime I want my macro to check the data.
I know it's possible to automate this process and I suspect/hope it's very simple.

The code I'm using is as follows:

Sub test()
If [A1] = 1 Then
[B1] = "1"
ElseIf [A1] = 2 Then
[B1] = "2"
ElseIf [A1] = 3 Then
[B1] = "3"
ElseIf [A1] = 4 Then
[B1] = "4"
ElseIf [A1] > 4 Then
[B1] = "Wrong Entry"
ElseIf [A1] < 0 Then
[B1] = "Wrong Entry"
End If
End Sub

My experience with Visual Basic is no greater then the above lines of code so I'd apreciate it if you could keep it simple

Tim

Edit: I forgot to tell you, cell A1 is a pulldown list, created with the validation>list option.

 

[onlyadrafter]

Hello and welcome to the board,

If you go to the VB COde Window, select the relevant sheet, you can have the following

Private Sub Worksheet_Activate()
'your code here
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'your code here
End Sub

The wroksheet activate will run the code when you select this sheet.

The Worksheet_change will run the code when you make a change anywhere in this sheet.

Is this explained enough?

 

[Arcadian]

wow, that was quick.

Yeah that worked perfectly. Thanks

 

[Arcadian]

Actually, although it does the trick, it slows down the process bigtime since it runs the macro everytime I update the worksheet. I only want it to run that macro when I update cell A1.
Is there a way to do this?

 

[onlyadrafter]

Hello,

Yes, no problem use this code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
'your code here
End If
End Sub

Any better?

 

[Arcadian]

Awesome, that's what I was looking for.
Thanks again.

 

[zakavelli]

Hi like the original poster my VBA knowledge is very limited. I am trying to get a macro to hide columns based on a cell value. The cell value is inputted manually and based on this the rows should hide. Basically if the cell d3 is equal to zero then all rows show, but if the cell value is greater than 0 then the rows 25:75 are hidden.

I have managed to put together some code based on googling around, and using this forum, and just playing around with code, and luckily for me I do not get any errors.

I've put the VBA into the VBA window by right clicking the sheet tab, and clicking view source using General and Declarations. The code I have used is shown below

Private Sub Worksheet_Activate()
If Cells(3, 4).Value > 0 Then
  ActiveSheet.Rows("25:75").Hidden = True
ElseIf Cells(3, 4).Value = 0 Then
  ActiveSheet.Rows("25:75").Hidden = False
End If
End Sub
 
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells = "$D$10" > 0 Then
    ActiveSheet.Rows("25:75").Hidden = True
ElseIf Target.Cells = "$D$10" = 0 Then
  ActiveSheet.Rows("25:75").Hidden = False
End If
End Sub

And I've saved a macro called 'run' in the macro VBA editor window

with the following code:

Sub run()
If Cells(3, 4) > 0 Then
  ActiveSheet.Rows("25:75").Hidden = True
ElseIf Cells(3, 4) = 0 Then
  ActiveSheet.Rows("25:75").Hidden = False
End If
End Sub

I want the macro to run in the background continuously. Now what happens is if I change the value in d3 from 0 to say '3' nothing happens, where it should hide row 25:75. However I can run the macro manually and it works. but when I then change the value from '3' back to 0 it automatically shows the row 25:75, which it should.

Where it works automatically one way, it doesn't work the other way.


Please can you help?

Thanks,

Zak

 

[Ruddles]

You would do better to start a new thread as this isn't anything to do with the original poster's problem and as he appears to have solved it, there may not be many people watching this thread now.

Hardly anyone will notice this post, so start a new thread and you will get replies, I'm sure.

 

[zakavelli]

Thanks Ruddles, Ill give it a go

 

[treepanic]

Hello, all! I have a very similar situation except I can't get the second part to work. I have an order form with certain items that need to be ordered in quantities of six. The "Worksheet_Activate()" bit works; if I click into 'Sheet 2' and back into 'Sheet 1', the macro auto-runs and rounds the quantities in the select cells to multiples of 6.

Here's my code:

Private Sub Worksheet_Activate() 'This part is working fine
For Each cell In [N46:N51, N56:N59]
If cell = " " Then Exit Sub
cell.Value = Application.MRound(cell.Value, 6)
Next cell
End Sub


Private Sub Worksheet_Change(ByVal Target As Range) 'This part isn't working
For Each cell In [N46:N51, N56:N59]
If cell = " " Then Exit Sub
cell.Value = Application.MRound(cell.Value, 6)
Next cell
End Sub

The error I receive is "Run-time error '28': Out of stack space"; when I click to debug, "For Each cell In [N46:N51, N56:N59]" (under 'Worksheet_Change...") is highlighted.

Any ideas?

Thanks!