Re: Excel will not recognise Macro

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Don Guillett (donaldb_at_281.com)
Date: 03/24/05 

Date: Thu, 24 Mar 2005 15:06:19 -0600

glad to help 

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Mark Wright" <BWDLandscapes@ntlworld.com> wrote in message
news:7zC0e.147$1K4.57@newsfe3-gui.ntli.net...
> Thank you Don & Julie. I shall beat myself with a wet fish for being so
> stupid and missing the obvious.
> Keep up the excellent work
> Many thanks
> Mark
>
> "JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
> news:eSweUUIMFHA.3832@TK2MSFTNGP12.phx.gbl...
> > Hi Mark
> >
> > the code is against a Worksheet_Change event - which means that it will
> run
> > automatically when a value in column F (other than F1) changes ...
> > so to test the code, change a value in F2 and see what happens
> >
> > oh, also this code should be pasted against the work*** (rgiht mouse
> click
> > on the appropriate *** tab and choose view / code, post it there)
itself
> > not in a module created from insert/module on the menu
> >
> >
> > Cheers
> > JulieD
> >
> >
> > "Mark Wright" <BWDLandscapes@ntlworld.com> wrote in message
> > news:Gyz0e.102$1K4.36@newsfe3-gui.ntli.net...
> > >
> > > Ladies & Gentlemen,
> > > A small problem which is either me being stupid or Excel.
> > >
> > > I have borrowed some code from David McRichie's site which I have then
> > > pasted into a Module in the VBA Editor window. When I click on the Run
> > > button to run the code Excel brings up the Macros dialog box asking me
> to
> > > name and create a new macro, in other words it is not recognising my
> > > module.
> > > The problem seems to be the argument "ByVal Target As Range". Excel
has
> no
> > > problem recognising the macro without the argument, but this means
that
> > > the
> > > macro doesn't work.
> > > The code is as below.
> > >
> > > "Private Sub Worksheet_Change(ByVal Target As Range)
> > >    Dim newSht As String, oldSht  As String
> > >    Dim wsOld As Work***, wsNew As Work***
> > >    If Target.Column <> 6 Or Target.Row = 1 Then Exit Sub
> > >    oldSht = Active***.Name
> > >    Set wsNew = Active***
> > >    newSht = Target.Text
> > >    On Error Resume Next
> > >    Sheets(newSht).Activate
> > >    If Err.Number = 0 Then    'sheetname already exists
> > >       Sheets(oldSht).Activate
> > >       Exit Sub
> > >       End If
> > >    On Error Resume Next
> > >  'Create New ***
> > >    Sheets.Add After:=Sheets(Sheets.Count)  '-- place at end
> > >    Active***.Name = newSht
> > >    Set wsNew = Active***
> > >    wsNew.Cells(1, 1) = "'" & newSht  'name of new *** into cell
> > >  '  Sheets(Sheets.Count).Activate  'try to show last tab
> > >    Sheets(oldSht).Activate
> > > End Sub"
> > >>
> > >> Many thanks in anticipation
> > >> Mark Wright
> > >>
> > >>
> > >
> > >
> >
> >
>
>
Quantcast