Jumat, 31 Mei 2013

Re: [ExcelVBA] auto run macro when link formula change

 

You don't say whether it's a form control or an Active X control.

For a form control, you might be stuck.

For an Active X control, you can use the ComboBox_Change event to pick up
changes made via the combo box. This will also fire if you change the
linked cell directly, because that changes the combo box value..

Regards, Dave S

----- Original Message -----
From: "Tayyab" <sheikhtayyab@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Friday, May 31, 2013 10:50 PM
Subject: [ExcelVBA] auto run macro when link formula change

hi
I have a sheet that have a combo box selection menu to select code, when
code is selected sheet auto changes value. then also have a macro that I
want to run . macro only run when I change any cell value, but not run when
changing the combo option.
I make attach code. Please help

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("g6:i10") 'combo box link cell

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Call HideColumns 'macro call that to hided black columns

End If
End Sub

Thanks & regards

Tayyab

[Non-text portions of this message have been removed]

------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
here: http://www.mousetrax.com/techtrax to enter the ezine, then search the
ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------Yahoo! Groups Links

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1432 / Virus Database: 3184/5871 - Release Date: 05/31/13

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (4)
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....
Diposting oleh EXCEL FEED LEARNING di 16.35 0 komentar

Re: [ExcelVBA] auto run macro when link formula change

 

I think what you're saying is that you have a combobox on the worksheet (not in
a userform)
and your Worksheet_Change event works when you change a cell value, but not when
you change the combobox selection.

If that's the case, then there's nothing you can do about it.
The VALUE of the combobox is NOT the selection, but the LINK to the combobox
values.
Technically, what you SEE in the cell is actually a property of the combobox,
not the VALUE of the cell.

changing the selection does not change the LINK, therefore, the event doesn't
recognize that there has been any "change" to the worksheet.

You MIGHT be able to set up a Public variable.
Then utilize a SelectionChange event to compare the combobox property to the
public variable.
If it does not match, then update the variable and run your macro.
This would then run whenever you select ANY cell.

 
Paul
-----------------------------------------
"Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can." - John Wesley
-----------------------------------------

________________________________
From: Tayyab <sheikhtayyab@yahoo.com>
To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
Sent: Fri, May 31, 2013 9:12:51 AM
Subject: [ExcelVBA] auto run macro when link formula change

 
hi
I have a sheet that have a combo box selection menu to select code, when code is
selected sheet auto changes value. then also have a macro that I want to run .
macro  only run when I change any cell value, but not run when changing the
combo option.
I make attach code.         Please help

  
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
 
    Set KeyCells = Range("g6:i10") 'combo box link cell

   
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
 
        Call HideColumns 'macro call that to hided black columns
      
    End If
End Sub

Thanks & regards

Tayyab

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....
Diposting oleh EXCEL FEED LEARNING di 06.26 0 komentar

RES: [ExcelVBA] auto run macro when link formula change

 

Try using event Calculate instead of Change

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Call HideColumns

End Sub

De: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] Em nome de
Tayyab
Enviada em: sexta-feira, 31 de maio de 2013 09:51
Para: ExcelVBA@yahoogroups.com
Assunto: [ExcelVBA] auto run macro when link formula change

hi
I have a sheet that have a combo box selection menu to select code, when
code is selected sheet auto changes value. then also have a macro that I
want to run . macro only run when I change any cell value, but not run when
changing the combo option.
I make attach code. Please help

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

Set KeyCells = Range("g6:i10") 'combo box link cell

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

Call HideColumns 'macro call that to hided black columns

End If
End Sub

Thanks & regards

Tayyab

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (3)
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....
Diposting oleh EXCEL FEED LEARNING di 06.20 0 komentar

[ExcelVBA] auto run macro when link formula change

 

hi
I have a sheet that have a combo box selection menu to select code, when code is selected sheet auto changes value. then also have a macro that I want to run . macro  only run when I change any cell value, but not run when changing the combo option.
I make attach code.         Please help

  
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
 
    Set KeyCells = Range("g6:i10") 'combo box link cell

   
    If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
 
        Call HideColumns 'macro call that to hided black columns
      
    End If
End Sub

Thanks & regards

Tayyab

[Non-text portions of this message have been removed]

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (1)
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___
READ MORE....
Diposting oleh EXCEL FEED LEARNING di 05.50 0 komentar

[smf_addin] Digest Number 2638

8 New Messages

Digest #2638
1a
Re: #NAME problem for smfGetOptionQuotes() function by "terminal3screensdesk" terminal3screensdesk
1c
Re: #NAME problem for smfGetOptionQuotes() function by "terminal3screensdesk" terminal3screensdesk
2a
Re: need help - WSJ data quote by "weekeewawa" weekeewawa
2b
Re: need help - WSJ data quote by "Randy Harmelink" rharmelink
2c
Re: need help - WSJ data quote by "weekeewawa" weekeewawa
2d
Re: need help - WSJ data quote by "Randy Harmelink" rharmelink

Messages

Thu May 30, 2013 6:45 am (PDT) . Posted by:

"terminal3screensdesk" terminal3screensdesk

With =RCHGetElementNumber("Version&quot;) I get:

Stock Market Functions add-in, Version 2.0k

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> What you list below can't be what you entered. The "(2013,6,1)" gets
> flagged by EXCEL as a formula error, which it is. This worked fine for me:
>
> =smfGetYahooOptionQuote("SPY","C",DATE(2013,6,22),160,"b")
>
> However, if you enter that into your spreadsheet and get a #NAME! error,
> the most likely reason is that the add-in isn't installed properly, or that
> you have an earlier version of the add-in that doesn't have that function
> in it.
>
> What do you get with:
>
> =RCHGetElementNumber("Version&quot;)
>
> On Wed, May 29, 2013 at 1:19 PM, terminal3screensdesk
> <lsbenedict@...>wrote:
>
> >
> > I have not been able to successfully use the smfGetOptionQuotes()
> > function. Regardless of the formula I enter, "#NAME" is returned.
> >
> > For example, when I entered
> > =smfGetYahooOptionQuote("SPY","C",(2013,6,1),160,"b") "#NAME" was returned.
> >
> > I looked at the documentation files to try to find a solution and didn't
> > find anything there. I understand that "#NAME" means that the link is
> > unresolved and made sure the links have the RCH_Stock_Market_Functions.xla
> > source.
> >
> > I also searched for similar questions and didn't find anything that
> > answered the specific problem I am having.
> >
> > Please let me know how I can fix this problem.
> >
>

Thu May 30, 2013 8:01 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

That's a very old copy of the add-in. It won't have any of the option
functions.

On Thu, May 30, 2013 at 6:45 AM, terminal3screensdesk
<lsbenedict@gmail.com>wrote:

> With =RCHGetElementNumber("Version&quot;) I get:
>
> Stock Market Functions add-in, Version 2.0k
>

Thu May 30, 2013 10:55 am (PDT) . Posted by:

"terminal3screensdesk" terminal3screensdesk

Ok, where can I download the most recent version?

Thank you.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> That's a very old copy of the add-in. It won't have any of the option
> functions.
>
> On Thu, May 30, 2013 at 6:45 AM, terminal3screensdesk
> <lsbenedict@...>wrote:
>
> > With =RCHGetElementNumber("Version&quot;) I get:
> >
> > Stock Market Functions add-in, Version 2.0k
> >
>

Thu May 30, 2013 10:59 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

From the FILES area of the group, in the "Add-In Files" folder.

On Thu, May 30, 2013 at 10:55 AM, terminal3screensdesk <lsbenedict@gmail.com
> wrote:

> Ok, where can I download the most recent version?
>

Thu May 30, 2013 7:08 am (PDT) . Posted by:

"weekeewawa" weekeewawa

hello randy,

that 2 technique is just amazing! everything just fits in. Can this 2 techniques are the only 2 needed to solve all duplication extraction problems?

However, there is still 1 last problem

the ''Depreciation'&#39; still gives problems

after i tried using

=RCHGetTableCell("http://quotes.wsj.com/"&A1&"/financials/annual/income-statement",0,">Depreciation")

which gives me ''Depreciation and Amortization Expenses'' still

or

=RCHGetTableCell("http://quotes.wsj.com/"&$A$1&"/financials/annual/income-statement",
0,"Depreciation","Depreciation&quot;)

which gives me a blank.

Something wrong with my coding?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> On Wed, May 29, 2013 at 10:31 PM, weekeewawa <weekeewawa@...> wrote:
>
> >
> > http://quotes.wsj.com/SG/XSES/O23/financials/annual/income-statement
> >
> > 1a) under Depreciation line
> >
> > Depreciation 12,767.0 11,530.0 10,047.0 10,958.0
> > 10,525.0
> >
> > thats the source code that i see. <td class="rowTitle">Depreciation</td>
> > and would like to extract this row. but it just gives me ERROR
> >
>
> Your formula doesn't give me an error:
>
> =RCHGetTableCell("http://quotes.wsj.com/
> "&A1&"/financials/annual/income-statement",0,"Depreciation&quot;)
>
> But it doesn't go to that line, because that is not the first
> "Depreciation&quot; string on the web page. Just looking at what you have listed
> there, what might make it unique? How about:
>
> =RCHGetTableCell("http://quotes.wsj.com/
> "&A1&"/financials/annual/income-statement",0,">Depreciation")
>
> 1b) the problem lies in this row
> >
> > Net Income (99,436.0) 23,334.0 50,069.0 69,063.0
> > 86,925.0
> >
> > where the code i keyed
> >
> > =RCHGetTableCell("http://quotes.wsj.com/"&A1&"/financials/annual/income-statement",0,"Net
> > Income")
> >
> > gives me ''Consolidated Net Income'&#39; Instead of ''Net Income'&#39;
> >
>
> Yup. So how can you make your search string unique? Or what search string
> can you look for first, so that you are past the "Consolidated Net Income"
> string, before looking for "Net Income"?
>
> You could even just look for the second occurrence of "Net Income":
>
> =RCHGetTableCell("http://quotes.wsj.com/
> "&A1&"/financials/annual/income-statement",
> 0,"Net Income","Net Income")
>
> 1c) the problem lies in
> >
> > Discontinued Operations - 0.0 0.0 0.0 0.0
> >
> > where the code i keyed
> >
> > =RCHGetTableCell("http://quotes.wsj.com/"&A1&"/financials/annual/income-statement",0,"Discontinued
> > Operations")
> >
> > gives me ''Extraordinaries & Discontinued Operations&#39;' insteaad of
> > ''Discontinued Operation''
> >
>
> Right. See the comments on the previous two problems. Unique, or more
> search strings.
>
>
>
> > http://quotes.wsj.com/SG/XSES/O23/financials/annual/balance-sheet
> >
> > 2a) as u can see from below
> >
> >
> Again, you either need a unique search string, or use multiple search
> strings to position yourself properly.
>
> can u provide some example in a form of the codes?
> > I have come across similar problems numerous times and i remembered once u
> > gave me the > trick
> > but for this case
> >
> > =RCHGetTableCell("http://quotes.wsj.com/"&A1&"/financials/annual/cash-flow",0,"Investment
> > Tax Credit>")
> >
> > gives me an error
> >
> > so is there any codes for the Extraordinaries and Investment Tax Credit?
> >
>
> Wrong location of HTML code. It needs to match what is in the HTML source
> code:
>
> =RCHGetTableCell("http://quotes.wsj.com/"&A1&"/financials/annual/cash-flow",0,">Investment
> Tax Credit")
>
> Or, to find the second occurrence:
>
> =RCHGetTableCell("http://quotes.wsj.com/
> "&$A$1&"/financials/annual/cash-flow",
> 0,"Investment Tax Credit","Investment Tax Credit")
>
> All of your issues relate to non-unique search strings. I've given you two
> examples above, that you should be able to apply to all the others.
>

Thu May 30, 2013 8:07 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

No. The function allows the use of up to four search strings. Personally, I
wouldn't use "Net Income" twice. If they ever add a meta tag to the web
page to describe its contents, it might mean you need to use three of them
to get to what you want. A better technique is to find a string in the
source code of the web page that positions you in front of the table, then
the second string gets you to the line of the table.

As I said, do a search of the source code for either "Depreciation&quot; or
">Depreciation" and you'll see it doesn't get you to where you want to be.

On Thu, May 30, 2013 at 7:08 AM, weekeewawa <weekeewawa@yahoo.com> wrote:

>
> that 2 technique is just amazing! everything just fits in. Can this 2
> techniques are the only 2 needed to solve all duplication extraction
> problems?
>
> However, there is still 1 last problem
>
> the ''Depreciation'&#39; still gives problems
>
> after i tried using
>
> =RCHGetTableCell("http://quotes.wsj.com/
> "&A1&"/financials/annual/income-statement",0,">Depreciation")
>
> which gives me ''Depreciation and Amortization Expenses'' still
>
> or
>
> =RCHGetTableCell("http://quotes.wsj.com/
> "&$A$1&"/financials/annual/income-statement",
> 0,"Depreciation","Depreciation&quot;)
>
> which gives me a blank.
>
> Something wrong with my coding?
>

Thu May 30, 2013 8:54 am (PDT) . Posted by:

"weekeewawa" weekeewawa

hello randy.
I GOT IT! so the best is actually to follow exactly what they showed on the page source

for example the one shows ''depreciation<&#39;' so i just use it

so i suppose,

this will be the best source code to use then?

=RCHGetTableCell("http://quotes.wsj.com/"&A1&"/financials/annual/income-statement",0,">Depreciation")

for the 2 or more search strings, i should actually avoid it?

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> No. The function allows the use of up to four search strings. Personally, I
> wouldn't use "Net Income" twice. If they ever add a meta tag to the web
> page to describe its contents, it might mean you need to use three of them
> to get to what you want. A better technique is to find a string in the
> source code of the web page that positions you in front of the table, then
> the second string gets you to the line of the table.
>
> As I said, do a search of the source code for either "Depreciation&quot; or
> ">Depreciation" and you'll see it doesn't get you to where you want to be.
>
> On Thu, May 30, 2013 at 7:08 AM, weekeewawa <weekeewawa@...> wrote:
>
> >
> > that 2 technique is just amazing! everything just fits in. Can this 2
> > techniques are the only 2 needed to solve all duplication extraction
> > problems?
> >
> > However, there is still 1 last problem
> >
> > the ''Depreciation'&#39; still gives problems
> >
> > after i tried using
> >
> > =RCHGetTableCell("http://quotes.wsj.com/
> > "&A1&"/financials/annual/income-statement",0,">Depreciation")
> >
> > which gives me ''Depreciation and Amortization Expenses'' still
> >
> > or
> >
> > =RCHGetTableCell("http://quotes.wsj.com/
> > "&$A$1&"/financials/annual/income-statement",
> > 0,"Depreciation","Depreciation&quot;)
> >
> > which gives me a blank.
> >
> > Something wrong with my coding?
> >
>

Thu May 30, 2013 10:45 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I would use:

=RCHGetTableCell("http://quotes.wsj.com/
"&A1&"/financials/annual/income-statement",0,">Depreciation")
=RCHGetTableCell("http://quotes.wsj.com/
"&A1&"/financials/annual/income-statement",
0,">Depreciation&quot;,">Depreciation")

...depending on which line item I wanted:

Depreciation & Amortization Expense
Depreciation

You could also use the whole label for each (bracketed by ">" and "<", as
coded on the web page):

=RCHGetTableCell("http://quotes.wsj.com/
"&A1&"/financials/annual/income-statement",
0,">Depreciation & Amortization Expense<")
=RCHGetTableCell("http://quotes.wsj.com/
"&A1&"/financials/annual/income-statement",0,">Depreciation<")

On Thu, May 30, 2013 at 8:54 AM, weekeewawa <weekeewawa@yahoo.com> wrote:

> hello randy.
> I GOT IT! so the best is actually to follow exactly what they showed on
> the page source
>
> for example the one shows ''depreciation<&#39;' so i just use it
>
> so i suppose,
>
> this will be the best source code to use then?
>
> =RCHGetTableCell("http://quotes.wsj.com/
> "&A1&"/financials/annual/income-statement",0,">Depreciation")
>
> for the 2 or more search strings, i should actually avoid it?
>
READ MORE....
Diposting oleh EXCEL FEED LEARNING di 03.34 0 komentar
Langganan: Postingan (Atom)