Autorun Sub WriteToTextFile ()

[PBMClubber]

Hi,

I can find a lot of information how to autorun macros and subs when opening Excel 2007.

I have one VBA sub "WriteTxt ()" which writes information from excel to text file. It works only when pressing F5 in VBA window.

Is it possible to get it running automatically by entering a value into a cell?

[sweep]

Hi,

How about something like this, which needs to go in the workbook area of the VBA project.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" And Sh.Name = "Sheet1" And Target.Value = 123 Then Call WriteTxt
End Sub

Obviously, amend the cell address, sheet name and value to your requirements

[PBMClubber]

Should it look like this?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" And Sh.Name = "Sheet1" And Target.Value = 123 Then Call WriteTxt
End Sub


Sub WriteTxt()
    ActiveWorkbook.SaveAs "C:\OUCHPut.txt", 21
End Sub

I tried this too:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Intersect(Target, Range("A1")) And Target.Value = 123 Then Call WriteTxt
End Sub


Sub WriteTxt()
    ActiveWorkbook.SaveAs "C:\OUCHPut.txt", 21
End Sub

[sweep]

I think that your WriteTxt() macro should go in a standard module, not in the worksheet area.

[snb]

Put this code in the sheetmodule
There's no need for a separate macro.

Private Sub Worksheet_Change(ByVal Target As Range)
  Target.Address ="$A$1" and Target.Value="#" Then ThisWorkbook.SaveAs "C:\examlple.txt", 21
End Sub

See the attachment.
NB. the macro will be executed on the moment you enter 'enter' after entering # in A1.