$$Excel-Macros$$ Extraction desire text data from the cell by formula
Dear all, I need to extract the partial contents of a cell. My excel sheet contains the data in the cell A:A which is very complex format data . form this data , I want to get the data as I color marks form B2:G2. Please check the attachments. If I get any partial help I will be glad also .. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. notice.xlsx Description: MS-Excel 2007 spreadsheet
$$Excel-Macros$$ Re: need last value
=IF(D3="N/A",INDEX($D$1:$D$13,LARGE(IF($D$1:D3<>"N/A",ROW($D$1:D3)),ROW(D3:D3)-ROW(D2:D2)),1),D3) Ctrl + shift + enter Mandeep Baluja Excel Specialist. On Thursday, November 20, 2014 10:55:44 AM UTC+5:30, Rohan Young wrote: > > Dear Group, > > Please see the attachment for my requirement > > Regards > > ROHAN > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Query of Excel get last not filled data.xlsx Description: MS-Excel 2007 spreadsheet
$$Excel-Macros$$ need last value
Dear Group, Please see the attachment for my requirement Regards ROHAN -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. Last Value to pick.xlsx Description: MS-Excel 2007 spreadsheet
Re: $$Excel-Macros$$ Add and remove text box on ever time button click.
Hello Paul, Thanks for reply Are you saying that when you hit "Add" you want to add another row of (5) textboxes? Yes, I want to add another row of (5) TextBoxes. And when you hit "Remove", it should remove the row of (5) boxes? Yes, I want to remove row of (5) tesxtboxex. If you remove the second row, what do you want to have happen to the rest? (third row move up to row 2 or ??) Yes, third row move up to row 2. Is there a limit to the number of rows? Yes, for now limit should be 12 rows of text boxes what happens when you exceed the size of the userform? in that case user form increasable accordingly or scroll bar will work. do you want the macro to increase the size of the userform? I want macro for add textboxes on Add button click every time and remove added textboxes on Remove button click. Thanks again for reply, Please help On Wed, Nov 19, 2014 at 6:07 PM, Paul Schreiner wrote: > Your userform currently has (3) rows of(5) textboxes. > > Are you saying that when you hit "Add" > you want to add another row of (5) textboxes? > > And when you hit "Remove", it should > remove the row of (5) boxes? > > If you remove the second row, what do you want to have happen to the rest? > (third row move up to row 2 or ??) > > Is there a limit to the number of rows? > what happens when you exceed the size of the userform? > do you want the macro to increase the size of the userform? > > *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:* Rupesh Patil > *To:* excel-macros@googlegroups.com > *Sent:* Wednesday, November 19, 2014 3:24 AM > *Subject:* Re: $$Excel-Macros$$ Add and remove text box on ever time > button click. > > Please Experts help me out. > > On Tue, Nov 18, 2014 at 5:07 PM, Rupesh Patil > wrote: > > Hello Experts, > > any help? > > > On Mon, Nov 17, 2014 at 4:10 PM, Rupesh Patil > wrote: > > Hello experts, > > I need your help,I have one userform which attached with mail for your > reference, what I need is add text boxes vertical on button every time > click "Add Button" and remove this text boxes every time on "Remove Button" > click. and Add-Remove row accordingly in sheet. > > -- > Thanks & Regards, > Rupesh Patil, > (9970196189) > . > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroups.com. > To post to this group, send email to excel-macros@googlegroups.com. > Visit this group at http://groups.google.com/group/excel-macros. > For more options, visit https://groups.google.com/d/optout. > > > > > -- > Thanks & Regards, > Rupesh Patil, > (9970196189), > Ayushman Pvt. Ltd. > > > > > -- > Thanks & Regards, > Rupesh Patil, > (9970196189), > Ayushman Pvt. Ltd. > > -- > Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s > =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > 2) Don't post a question in the thread of another member. > 3) Don't post questions regarding breaking or bypassing any security > measure. > 4) Acknowledge the responses you receive, good or bad. > 5) Jobs posting is not allowed. > 6) Sharing copyrighted material and their links is not allowed. > > NOTE : Don't ever post confidential data in a workbook. Forum owners and > members are not responsible for any loss. > --- > You received this message because you are subscribed to the Google Groups > "MS EXCEL AND VBA MACROS" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to excel-macros+unsubscr...@googlegroup
Re: $$Excel-Macros$$ Error in VBA
What module did you put it in?
(it should be in a sheet module)
also, the part that says: Weekday(Date)
today (Wednesday) is weekday #4, so Weekday(Date) = 2 will be false
also, for debugging purposes:
in the VBE window, right-click on the menu area and turn on the Edit and Debug
icon sets.
I usually "park" them on the top menu row.
the "step into" button (to the right of the "hand" icon)
will allow you to step one line at a time through a macro.
in a macro:
click in the vertical grey border to the left of an EXECUTABLE macro line (not
a comment).
a small circle should appear, and the line should be highlighted in the same
color.
This is setting a "breakpoint".
When a macro runs, it will stop at this point.
you can then use the menu buttons (or function keys) to step through the macro
from this point.
you can also right-click variables or even things like "weekday(date)" and
select "Add watch".
this will put the "run-time" value of the variable or function in a Watch
Window.
During debug breakpoint, this window will show you the value of the variable or
function.
so, if you put a watch on weekday(date), as you step through the macro, it will
show that the value is 4 (not 2)
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: USMAN TARIQ
>To: excel-macros@googlegroups.com
>Sent: Wednesday, November 19, 2014 3:02 PM
>Subject: Re: $$Excel-Macros$$ Error in VBA
>
>
>
>Private Sub Worksheet_Activate()
>
>
>'Private Sub TestFileExistence()
>
>
> If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
> Exec.mhtml") And _
> Weekday(Date) = 2 Then
>
> Range("P4").Value = "X"
>
>
>End If
>
>End Sub
>
>
>i put like this , its not doing any thing :(
>
>
>On Wed, Nov 19, 2014 at 2:59 PM, USMAN TARIQ wrote:
>
>Private Sub Worksheet_Activate()
>>
>>
>>End Sub
>>
>>
>>so do i need to put my code between the above two ?
>>
>>
>>i put the
>>
>>
>>On Wed, Nov 19, 2014 at 1:35 PM, Paul Schreiner
>>wrote:
>>
>>Ah yes...
>>>More tricks of the trade.
>>>Soon, you'll be making this look easy!
>>>
>>>first:
>>>As I mentioned at the beginning of the last email, the biggest struggle for
>>>someone new is "terminology and communication".
>>>
>>>The PROBLEM you were having wasn't "difficult".
>>>what was difficult was wading through what you wanted to do and "translate"
>>>it to standard terminology and getting it into perspective.
>>>
>>>you had stated that you were using something called
>>>FileFolderExists()
>>>
>>>that's not "standard VBA" but a custom function that there is No WAY we
>>>could know what that did.
>>>
>>>So I think most people simply decided that they didn't want to try to
>>>untangle it.
>>>
>>>not that it was your FAULT, it's just that you have to learn to step back
>>>and try to learn to describe your situation as if you're talking to someone
>>>that isn't doing the EXACT SAME job you are, but something similar.
>>>
>>>
>>>and now for the TRICKS!
>>>
>>>
>>>When you're in the VB Editor, double-click on one of the Sheet modules
>>>like the one called Sheet1()
>>>
>>>at the top of the edit pane/panel, notice that there are two pull-down menus.
>>>
>>>The one on the left usually says something like "General"
>>>and the one on the right says something like "(Declarations)"
>>>
>>>Now, pull down the left-hand ("General") menu.
>>>There should only be one selection, called "Worksheet".
>>>Select it.
>>>
>>>Immediately, the editor will create an Event macro called:
>>>
>>>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>>>
>>>This is a macro that executes whenever you select a different cell on the
>>>sheet!
>>>it passes a Range variable called "Target" to the macro.
>>>
>>>So, in this macro, if you put something like:
>>>
>>>MsgBox Target.Value
>>>
>>>whenever you select a cell, it will display the value of the cell in a
>>>message box.
>>>
>>>Now, with "Worksheet" selected in the left box, pull down the right-hand
>>>menu.
>>>this is a list of all of the Event macros associated with the Worksheet
>>>object.
>>>
>>>Most names are obvious as to when they would run:
>>>Activate (when you select the sheet)
>>>Deactivate (when you LEAVE the sheet)
>>>BeforeDoubleclick (runs when you double-click a cell, before the "normal"
>>>double-click stuff)
>>>BeforeRightClick
>>>Change (runs when the value of a cell changes)
>>>SelectionChange
>>>
>>>selecting one of these will create the event macro with the proper syntax.
>>>If you want to know about it, simply type the name (like beforedoubleclick)
>>>in the upper-right help box and it should give you some assistance.
>>>
>>>this same type of "macro wizard" is available within the ThisWorkbook module.
>>>The difference
Re: $$Excel-Macros$$ Error in VBA
Private Sub Worksheet_Activate()
'Private Sub TestFileExistence()
If FileFolderExists("\\cf3.pepsico.pvt\psra\Output\BI4\PSRA Day Tracker -
Exec.mhtml") And _
Weekday(Date) = 2 Then
Range("P4").Value = "X"
End If
End Sub
*i put like this , its not doing any thing :(*
On Wed, Nov 19, 2014 at 2:59 PM, USMAN TARIQ
wrote:
> Private Sub Worksheet_Activate()
>
> End Sub
>
> so do i need to put my code between the above two ?
>
> i put the
>
> On Wed, Nov 19, 2014 at 1:35 PM, Paul Schreiner
> wrote:
>
>> Ah yes...
>> More tricks of the trade.
>> Soon, you'll be making this look easy!
>>
>> first:
>> As I mentioned at the beginning of the last email, the biggest struggle
>> for someone new is "terminology and communication".
>>
>> The PROBLEM you were having wasn't "difficult".
>> what was difficult was wading through what you wanted to do and
>> "translate" it to standard terminology and getting it into perspective.
>>
>> you had stated that you were using something called
>> FileFolderExists()
>>
>> that's not "standard VBA" but a custom function that there is No WAY we
>> could know what that did.
>>
>> So I think most people simply decided that they didn't want to try to
>> untangle it.
>>
>> not that it was your FAULT, it's just that you have to learn to step back
>> and try to learn to describe your situation as if you're talking to someone
>> that isn't doing the EXACT SAME job you are, but something similar.
>>
>>
>> and now for the TRICKS!
>>
>>
>> When you're in the VB Editor, double-click on one of the Sheet modules
>> like the one called Sheet1()
>>
>> at the top of the edit pane/panel, notice that there are two pull-down
>> menus.
>>
>> The one on the left usually says something like "General"
>> and the one on the right says something like "(Declarations)"
>>
>> Now, pull down the left-hand ("General") menu.
>> There should only be one selection, called "Worksheet".
>> Select it.
>>
>> Immediately, the editor will create an Event macro called:
>>
>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>>
>> This is a macro that executes whenever you select a different cell on the
>> sheet!
>> it passes a Range variable called "Target" to the macro.
>>
>> So, in this macro, if you put something like:
>>
>> MsgBox Target.Value
>>
>> whenever you select a cell, it will display the value of the cell in a
>> message box.
>>
>> Now, with "Worksheet" selected in the left box, pull down the right-hand
>> menu.
>> this is a list of all of the Event macros associated with the Worksheet
>> object.
>>
>> Most names are obvious as to when they would run:
>> Activate (when you select the sheet)
>> Deactivate (when you LEAVE the sheet)
>> BeforeDoubleclick (runs when you double-click a cell, before the "normal"
>> double-click stuff)
>> BeforeRightClick
>> Change (runs when the value of a cell changes)
>> SelectionChange
>>
>> selecting one of these will create the event macro with the proper syntax.
>> If you want to know about it, simply type the name (like
>> beforedoubleclick) in the upper-right help box and it should give you some
>> assistance.
>>
>> this same type of "macro wizard" is available within the ThisWorkbook
>> module.
>> The difference here is that instead of "Worksheet" in the upper right
>> pull-down,
>> you get "Workbook".
>> and there's several more events that are workbook related.
>> _Open
>> _BeforeSave
>> _AfterSave
>> _Activate
>> _Deactivate
>>
>> another "feature" is that if you have several macros in a module,
>> selecting the right-hand pull-down will show you the macro names and
>> allow you to "jump" to the macro instead of scrolling to them.
>> (really helpful when you have 5000 lines in 10 macros in a single module!)
>>
>> In the Thisworkbook and Sheet modules, the event macros that you've
>> written are BOLD
>>
>> hope this helps!
>>
>> *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:* USMAN TARIQ
>> *To:* excel-macros@googlegroups.com
>> *Sent:* Wednesday, November 19, 2014 1:04 PM
>>
>> *Subject:* Re: $$Excel-Macros$$ Error in VBA
>>
>> Oh God!!! ...You have no idea what you have done You are best ,
>> like i have literally posted my this big questions everywhere , nearly 6
>> forums and it was driving me nutsand you have so much patience
>> like you have no idea and nobody has yet replied and on the top in this
>> much clear detail . and on the top your code is also working !
>>
>>
>> Now last dum question , you have mention the automatic activation of the
>> code can be done through two fucntions
>>
>> 1)- Workbook_Open
>> 2)-Workbook_SheetActivate
>>
>> and i know you said to always keep this function code
Re: $$Excel-Macros$$ Error in VBA
Private Sub Worksheet_Activate()
End Sub
so do i need to put my code between the above two ?
i put the
On Wed, Nov 19, 2014 at 1:35 PM, Paul Schreiner
wrote:
> Ah yes...
> More tricks of the trade.
> Soon, you'll be making this look easy!
>
> first:
> As I mentioned at the beginning of the last email, the biggest struggle
> for someone new is "terminology and communication".
>
> The PROBLEM you were having wasn't "difficult".
> what was difficult was wading through what you wanted to do and
> "translate" it to standard terminology and getting it into perspective.
>
> you had stated that you were using something called
> FileFolderExists()
>
> that's not "standard VBA" but a custom function that there is No WAY we
> could know what that did.
>
> So I think most people simply decided that they didn't want to try to
> untangle it.
>
> not that it was your FAULT, it's just that you have to learn to step back
> and try to learn to describe your situation as if you're talking to someone
> that isn't doing the EXACT SAME job you are, but something similar.
>
>
> and now for the TRICKS!
>
>
> When you're in the VB Editor, double-click on one of the Sheet modules
> like the one called Sheet1()
>
> at the top of the edit pane/panel, notice that there are two pull-down
> menus.
>
> The one on the left usually says something like "General"
> and the one on the right says something like "(Declarations)"
>
> Now, pull down the left-hand ("General") menu.
> There should only be one selection, called "Worksheet".
> Select it.
>
> Immediately, the editor will create an Event macro called:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> This is a macro that executes whenever you select a different cell on the
> sheet!
> it passes a Range variable called "Target" to the macro.
>
> So, in this macro, if you put something like:
>
> MsgBox Target.Value
>
> whenever you select a cell, it will display the value of the cell in a
> message box.
>
> Now, with "Worksheet" selected in the left box, pull down the right-hand
> menu.
> this is a list of all of the Event macros associated with the Worksheet
> object.
>
> Most names are obvious as to when they would run:
> Activate (when you select the sheet)
> Deactivate (when you LEAVE the sheet)
> BeforeDoubleclick (runs when you double-click a cell, before the "normal"
> double-click stuff)
> BeforeRightClick
> Change (runs when the value of a cell changes)
> SelectionChange
>
> selecting one of these will create the event macro with the proper syntax.
> If you want to know about it, simply type the name (like
> beforedoubleclick) in the upper-right help box and it should give you some
> assistance.
>
> this same type of "macro wizard" is available within the ThisWorkbook
> module.
> The difference here is that instead of "Worksheet" in the upper right
> pull-down,
> you get "Workbook".
> and there's several more events that are workbook related.
> _Open
> _BeforeSave
> _AfterSave
> _Activate
> _Deactivate
>
> another "feature" is that if you have several macros in a module,
> selecting the right-hand pull-down will show you the macro names and allow
> you to "jump" to the macro instead of scrolling to them.
> (really helpful when you have 5000 lines in 10 macros in a single module!)
>
> In the Thisworkbook and Sheet modules, the event macros that you've
> written are BOLD
>
> hope this helps!
>
> *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:* USMAN TARIQ
> *To:* excel-macros@googlegroups.com
> *Sent:* Wednesday, November 19, 2014 1:04 PM
>
> *Subject:* Re: $$Excel-Macros$$ Error in VBA
>
> Oh God!!! ...You have no idea what you have done You are best ,
> like i have literally posted my this big questions everywhere , nearly 6
> forums and it was driving me nutsand you have so much patience
> like you have no idea and nobody has yet replied and on the top in this
> much clear detail . and on the top your code is also working !
>
>
> Now last dum question , you have mention the automatic activation of the
> code can be done through two fucntions
>
> 1)- Workbook_Open
> 2)-Workbook_SheetActivate
>
> and i know you said to always keep this function code code in ThisWorkbook
>
> *what should be the actually syxtax of this fucntions to be written ?*
>
> THANKS ! :)
>
>
>
> On Wed, Nov 19, 2014 at 12:22 PM, Paul Schreiner
> wrote:
>
> As someone new to VBA, the biggest struggle when asking for help
> is terminology and communication.
>
> YOU know what you see on the screen, but when you post a question, the
> people reading cannot see what you see.
>
> First, some terminology and "structure":
> Keep in mind that this description is
Re: $$Excel-Macros$$ Error in VBA
Ah yes...
More tricks of the trade.
Soon, you'll be making this look easy!
first:
As I mentioned at the beginning of the last email, the biggest struggle for
someone new is "terminology and communication".
The PROBLEM you were having wasn't "difficult".
what was difficult was wading through what you wanted to do and "translate" it
to standard terminology and getting it into perspective.
you had stated that you were using something called
FileFolderExists()
that's not "standard VBA" but a custom function that there is No WAY we could
know what that did.
So I think most people simply decided that they didn't want to try to untangle
it.
not that it was your FAULT, it's just that you have to learn to step back and
try to learn to describe your situation as if you're talking to someone that
isn't doing the EXACT SAME job you are, but something similar.
and now for the TRICKS!
When you're in the VB Editor, double-click on one of the Sheet modules
like the one called Sheet1()
at the top of the edit pane/panel, notice that there are two pull-down menus.
The one on the left usually says something like "General"
and the one on the right says something like "(Declarations)"
Now, pull down the left-hand ("General") menu.
There should only be one selection, called "Worksheet".
Select it.
Immediately, the editor will create an Event macro called:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
This is a macro that executes whenever you select a different cell on the sheet!
it passes a Range variable called "Target" to the macro.
So, in this macro, if you put something like:
MsgBox Target.Value
whenever you select a cell, it will display the value of the cell in a message
box.
Now, with "Worksheet" selected in the left box, pull down the right-hand menu.
this is a list of all of the Event macros associated with the Worksheet object.
Most names are obvious as to when they would run:
Activate (when you select the sheet)
Deactivate (when you LEAVE the sheet)
BeforeDoubleclick (runs when you double-click a cell, before the "normal"
double-click stuff)
BeforeRightClick
Change (runs when the value of a cell changes)
SelectionChange
selecting one of these will create the event macro with the proper syntax.
If you want to know about it, simply type the name (like beforedoubleclick) in
the upper-right help box and it should give you some assistance.
this same type of "macro wizard" is available within the ThisWorkbook module.
The difference here is that instead of "Worksheet" in the upper right pull-down,
you get "Workbook".
and there's several more events that are workbook related.
_Open
_BeforeSave
_AfterSave
_Activate
_Deactivate
another "feature" is that if you have several macros in a module,
selecting the right-hand pull-down will show you the macro names and allow you
to "jump" to the macro instead of scrolling to them.
(really helpful when you have 5000 lines in 10 macros in a single module!)
In the Thisworkbook and Sheet modules, the event macros that you've written are
BOLD
hope this helps!
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: USMAN TARIQ
>To: excel-macros@googlegroups.com
>Sent: Wednesday, November 19, 2014 1:04 PM
>Subject: Re: $$Excel-Macros$$ Error in VBA
>
>
>
>Oh God!!! ...You have no idea what you have done You are best , like
>i have literally posted my this big questions everywhere , nearly 6 forums
>and it was driving me nutsand you have so much patience like you
>have no idea and nobody has yet replied and on the top in this much clear
>detail . and on the top your code is also working !
>
>
>
>
>Now last dum question , you have mention the automatic activation of the code
>can be done through two fucntions
>
>
>1)- Workbook_Open
>2)-Workbook_SheetActivate
>
>
>and i know you said to always keep this function code code in ThisWorkbook
>
>
>what should be the actually syxtax of this fucntions to be written ?
>
>
>THANKS ! :)
>
>
>
>
>
>
>On Wed, Nov 19, 2014 at 12:22 PM, Paul Schreiner
>wrote:
>
>As someone new to VBA, the biggest struggle when asking for help is
>terminology and communication.
>>
>>YOU know what you see on the screen, but when you post a question, the people
>>reading cannot see what you see.
>>
>>First, some terminology and "structure":
>>Keep in mind that this description is more of a "lay" description, rather
>>than pure "geek speak", and therefore, may not be 100% technologically
>>accurate, but more of a practical description of concept!!!
>>
>>An Excel file is called a Workbook.
>>a SHEET (or tab) in an excel file is called a WorkSheet.
>>Worksheets have "properties".
>>all of the worksheets together are
Re: $$Excel-Macros$$ Error in VBA
Oh God!!! ...You have no idea what you have done You are best ,
like i have literally posted my this big questions everywhere , nearly 6
forums and it was driving me nutsand you have so much patience
like you have no idea and nobody has yet replied and on the top in this
much clear detail . and on the top your code is also working !
Now last dum question , you have mention the automatic activation of the
code can be done through two fucntions
1)- Workbook_Open
2)-Workbook_SheetActivate
and i know you said to always keep this function code code in ThisWorkbook
*what should be the actually syxtax of this fucntions to be written ?*
THANKS ! :)
On Wed, Nov 19, 2014 at 12:22 PM, Paul Schreiner
wrote:
> As someone new to VBA, the biggest struggle when asking for help
> is terminology and communication.
>
> YOU know what you see on the screen, but when you post a question, the
> people reading cannot see what you see.
>
> First, some terminology and "structure":
> Keep in mind that this description is more of a "lay" description, rather
> than pure "geek speak", and therefore, may not be 100% technologically
> accurate, but more of a practical description of concept!!!
>
> An Excel file is called a Workbook.
> a SHEET (or tab) in an excel file is called a WorkSheet.
> Worksheets have "properties".
> all of the worksheets together are called a Sheets "collection".
>
>
> On the VB editor screen (sometimes called VBE)
> You SHOULD have, on the left side, a VB Project Panel.
> In a brand new workbook, this panel will have a folder called "Microsoft
> Excel Objects"
>
> This is a list of the different type of VBA code "modules".
>
> within this folder is at a MINIMUM, one module which is usually labelled
> "Sheet1(Sheet1)"
> What this is saying is that a worksheet called "Sheet1" is NAMED "Sheet1"
> If you rename the tab/worksheet in Excel to something like "Data",
> you'll see this entry changed to:
> Sheet1(Data)
> These are specific types of modules called "Sheet Modules"
> code written in these modules are specific to the sheet and are not shared
> between sheets.
> There are some special rules that apply to code in these modules.
> In some cases, code written in a Sheet module cannot modify properties of
> the entire workbook.
>
>
> The last entry in this section is called "ThisWorkbook"
> This is a special type of Module that handles code that applies to the
> entire workbook, not specific WorkSheets.
>
> If you right-click in this area and select Insert->Module,
> the panel will show a new folder called "Modules"
> and the new module created will be called "Module1"
> (which can be changed in a "Properties" window)
> This is referred to as a "Standard" code module.
> This type of module can be used for code that applies to all sheets in the
> workbook, as well as the workbook itself.
>
> Some macros (called Subroutines or Functions) can be called on at any time.
>
> Others run "automatically" when an "Event" takes place.
> These are called, curiously enough, "Event Macros".
>
> Now, you can "open" a workbook, but you "activate" a workSHEET.
> Therefore, the "open" event for a workbook is called "Workbook_Open"
> and MUST be placed in the ThisWorkBook module!
> (since it applies to the workbook itself, not an individual sheet)
>
> Now, if you want something to happen every time you activate a specific
> workSHEET:
> like, clear previous filters, select cell A1, etc,
> these apply to a specific sheet, so in THAT SHEET MODULE,
> you'd create a Worksheet_Activate subroutine.
>
>
> Now, if you wanted to do this for ALL sheets (or most), instead of copying
> a Worksheet_Activate macro to every sheet, you can create a
> Workbook_SheetActivate event macro in the Thisworkbook module.
> ---
> So.. the answer to one of your statements/questions regarding a
> Workbook_Open macro not running is probably because this event ONLY runs if
> it is placed in the Thisworkbook module.
>
> -
>
> The Operating System (Windows, MAC, Unix) has a system for storing files.
> Hence, when dealing with files, Microsoft (and VBA) utilizes a
> "FileSystem" object.
>
> I usually create a variable fso (File System Object)
> and set it to be defined as a File System Object:
>
> Set fso = CreateObject("Scripting.FileSystemObject")
>
> Using this, I can check to see if a folder exists:
> if (fso.folderexists(foldername))
> or if a file exists:
> if (fso.fileexists(fullfilename))
>
> if the file or folder exists, then I can use methods like:
>
> fso.getfolder()
> fso.getfile()
>
> to get the attibutes and properties of the file or folder.
>
> you have a function:
> *Public Function FileFolderExists(strFullPath As String) As Boolean*
> * 'Author : Usman Tariq*
> * 'Macro Purpose: Check if a file or folder exists*
> * On Error GoTo EarlyExit*
> * If Not Dir(strFullPath, vbDirectory) = vbNullString Then
> FileFolderExists = True*
> *EarlyExit:*
> * On Error
Re: $$Excel-Macros$$ Error in VBA
As someone new to VBA, the biggest struggle when asking for help is terminology
and communication.
YOU know what you see on the screen, but when you post a question, the people
reading cannot see what you see.
First, some terminology and "structure":
Keep in mind that this description is more of a "lay" description, rather than
pure "geek speak", and therefore, may not be 100% technologically accurate, but
more of a practical description of concept!!!
An Excel file is called a Workbook.
a SHEET (or tab) in an excel file is called a WorkSheet.
Worksheets have "properties".
all of the worksheets together are called a Sheets "collection".
On the VB editor screen (sometimes called VBE)
You SHOULD have, on the left side, a VB Project Panel.
In a brand new workbook, this panel will have a folder called "Microsoft Excel
Objects"
This is a list of the different type of VBA code "modules".
within this folder is at a MINIMUM, one module which is usually labelled
"Sheet1(Sheet1)"
What this is saying is that a worksheet called "Sheet1" is NAMED "Sheet1"
If you rename the tab/worksheet in Excel to something like "Data",
you'll see this entry changed to:
Sheet1(Data)
These are specific types of modules called "Sheet Modules"
code written in these modules are specific to the sheet and are not shared
between sheets.
There are some special rules that apply to code in these modules.
In some cases, code written in a Sheet module cannot modify properties of the
entire workbook.
The last entry in this section is called "ThisWorkbook"
This is a special type of Module that handles code that applies to the entire
workbook, not specific WorkSheets.
If you right-click in this area and select Insert->Module,
the panel will show a new folder called "Modules"
and the new module created will be called "Module1"
(which can be changed in a "Properties" window)
This is referred to as a "Standard" code module.
This type of module can be used for code that applies to all sheets in the
workbook, as well as the workbook itself.
Some macros (called Subroutines or Functions) can be called on at any time.
Others run "automatically" when an "Event" takes place.
These are called, curiously enough, "Event Macros".
Now, you can "open" a workbook, but you "activate" a workSHEET.
Therefore, the "open" event for a workbook is called "Workbook_Open"
and MUST be placed in the ThisWorkBook module!
(since it applies to the workbook itself, not an individual sheet)
Now, if you want something to happen every time you activate a specific
workSHEET:
like, clear previous filters, select cell A1, etc,
these apply to a specific sheet, so in THAT SHEET MODULE,
you'd create a Worksheet_Activate subroutine.
Now, if you wanted to do this for ALL sheets (or most), instead of copying a
Worksheet_Activate macro to every sheet, you can create a
Workbook_SheetActivate event macro in the Thisworkbook module.
---
So.. the answer to one of your statements/questions regarding a Workbook_Open
macro not running is probably because this event ONLY runs if it is placed in
the Thisworkbook module.
-
The Operating System (Windows, MAC, Unix) has a system for storing files.
Hence, when dealing with files, Microsoft (and VBA) utilizes a "FileSystem"
object.
I usually create a variable fso (File System Object)
and set it to be defined as a File System Object:
Set fso = CreateObject("Scripting.FileSystemObject")
Using this, I can check to see if a folder exists:
if (fso.folderexists(foldername))
or if a file exists:
if (fso.fileexists(fullfilename))
if the file or folder exists, then I can use methods like:
fso.getfolder()
fso.getfile()
to get the attibutes and properties of the file or folder.
you have a function:
Public Function FileFolderExists(strFullPath As String) As Boolean
'Author : Usman Tariq
'Macro Purpose: Check if a file or folder exists
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists =
True
EarlyExit:
On Error GoTo 0
End Function
Normally a function stored in a "standard" module is only available to other
macros within the module.
By placing the "Public" at the beginning, you're instructing the compiler to
make this available to all modules.
This macro utilizes a Dir() function.
Basically, the Wizards of Microsoft wrote a function (much like your own) that
accepts a filename and attribute and returns a filename.
The Dir() function can be used to loop through the files in a folder that match
specific criteria (like checking all files ending in .xls)
but it doesn't have the capability to check dates.
To compare dates, you'd need to use the filesytem object.
in your case, I'd probably do something like:
Public Function FileFolderExists(strFullPath As String) As Boolean
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
On Error GoTo EarlyExit
FileFolde
Re: $$Excel-Macros$$ Error in VBA
GREAT!!
Now I think I know where to look.
Let me write a somewhat longer explanation and get back to you.
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: USMAN TARIQ
>To: excel-macros@googlegroups.com
>Sent: Wednesday, November 19, 2014 11:19 AM
>Subject: Re: $$Excel-Macros$$ Error in VBA
>
>
>
>i apologize for confusion, actually i am new to VBA . So here is the final
>thing i want
>
>
>i have 2 VBA code in two separate sheets , and then i have placed the
>declaration of the function (Private Sub TestFileExistence()) in the Module .
>this function is being used in the two VBA.
>what this code is doing is looking for files name and putting the X mark in
>the sheet
>
>
>Now there are these files that land in a folder everyday , and are Replaced by
>new Files . Lets say we have a Report A that delivered yesterday , and today
>we didnt get the today file, means we have the old yesterday , i want to show
>empty if the new today file is not there . But if the file in the folder is of
>Today then show the X mark
>
>
>attached is the screen shot
>
>
>you have advised me to use some code using Modified date , dont know where to
>put that code .
>
>
>
>
>Fillowing is the code in the Module
>
>
>
>
>Public Function FileFolderExists(strFullPath As String) As Boolean
>'Author : Usman Tariq
>'Macro Purpose: Check if a file or folder exists
>On Error GoTo EarlyExit
>If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists
> = True
>
>EarlyExit:
>On Error GoTo 0
>End Function
>
>
>
>
>
>
>On Wed, Nov 19, 2014 at 11:00 AM, Paul Schreiner
>wrote:
>
>What is not working?
>>Does it give an error?
>>
>>You've got several things in this email.
>>which are you referring to?
>>
>>You said:
>>i want i have 2 sheets in my work book, want to use the Private Sub
>>Workbook_Open() , its not working for me :(
>>
>>the number of sheets has absolutely nothing to do with creating a
>>Workbook_Open event macro.
>>Unless you're trying to put the macro in a Sheet module instead of the
>>ThisWorkbook module.
>>
>>please provide more information for what is happening (or not happening) and
>>what you WANT to happen.
>>
>>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: USMAN TARIQ
>>>To: excel-macros@googlegroups.com
>>>Sent: Wednesday, November 19, 2014 10:39 AM
>>>Subject: Re: $$Excel-Macros$$ Error in VBA
>>>
>>>
>>>
>>>please advise
>>>
>>>
>>>On Tue, Nov 18, 2014 at 12:50 PM, USMAN TARIQ
>>>wrote:
>>>
>>>Following is not woreking
PrivateSubWorkbook_Open()Dimws AsWorksheet ForEachws
InThisWorkbook.Worksheets Withws .EnableOutlining =True.Protect
UserInterfaceOnly:=True,AllowFiltering:=True,_
AllowFormattingColumns:=True,AllowInsertingRows:=TrueEndWithNextEndSub
On Tue, Nov 18, 2014 at 12:46 PM, USMAN TARIQ
wrote:
also i want i have 2 sheets in my work book, want to use the Private Sub
Workbook_Open() , its not working for me :(
>
>
>On Tue, Nov 18, 2014 at 11:20 AM, USMAN TARIQ
>wrote:
>
>is the following fine ?
>>
>>
>>Public Function FileFolderExists(strFullPath As String) As Boolean
>>'Author : Usman Tariq
>>'Macro Purpose: Check if a file or folder exists
>>On Error GoTo EarlyExit
>>If Not Dir(strFullPath, vbDirectory) = vbNullString Then
>> FileFolderExists = TrueAnd (DateDiff("d", f.datelastmodified, Now()) = 0)
>>
>>
>>EarlyExit:
>>On Error GoTo 0
>>End Function
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>On Tue, Nov 18, 2014 at 11:18 AM, USMAN TARIQ
>>wrote:
>>
>>following is the code in the Module
>>>
>>>
>>>Public Function FileFolderExists(strFullPath As String) As Boolean
>>>'Author : Usman Tariq
>>>'Macro Purpose: Check if a file or folder exists
>>>On Error GoTo EarlyExit
>>>If Not Dir(strFullPath, vbDirectory) = vbNullString Then
>>> FileFolderExists = True
>>>
>>>EarlyExit:
>>>On Error GoTo 0
>>>End Function
>>>
>>>
>>>Please advise where to put if (datediff("d",f.datelastmodified,now()) =
>>>0) then
>>>
>>>
>>
>
>>>
>>>--
>>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>>>=TIME(2,DO:IT,N:OW) ! Join official Facebo
Re: $$Excel-Macros$$ Error in VBA
i apologize for confusion, actually i am new to VBA . So here is the final
thing i want
i have 2 VBA code in two separate sheets , and then i have placed the
declaration of the function *(Private Sub TestFileExistence())* in the
Module . this function is being used in the two VBA.
what this code is doing is looking for files name and putting the X mark in
the sheet
Now there are these files that land in a folder everyday , and are Replaced
by new Files . Lets say we have a Report A that delivered yesterday , and
today we didnt get the today file, means we have the old yesterday , i want
to show empty if the new today file is not there . But if the file in the
folder is of Today then show the X mark
attached is the screen shot
you have advised me to use some code using Modified date , dont know where
to put that code .
Fillowing is the code in the Module
*Public Function FileFolderExists(strFullPath As String) As Boolean*
*'Author : Usman Tariq*
*'Macro Purpose: Check if a file or folder exists*
*On Error GoTo EarlyExit*
*If Not Dir(strFullPath, vbDirectory) = vbNullString Then
FileFolderExists = True*
*EarlyExit:*
*On Error GoTo 0*
*End Function*
On Wed, Nov 19, 2014 at 11:00 AM, Paul Schreiner
wrote:
> What is not working?
> Does it give an error?
>
> You've got several things in this email.
> which are you referring to?
>
> You said:
> i want i have 2 sheets in my work book, want to use the *Private Sub
> Workbook_Open() , its not working for me :(*
>
> the number of sheets has absolutely nothing to do with creating a
> Workbook_Open event macro.
> Unless you're trying to put the macro in a Sheet module instead of the
> ThisWorkbook module.
>
> please provide more information for what is happening (or not happening)
> and what you WANT to happen.
>
> *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:* USMAN TARIQ
> *To:* excel-macros@googlegroups.com
> *Sent:* Wednesday, November 19, 2014 10:39 AM
> *Subject:* Re: $$Excel-Macros$$ Error in VBA
>
> please advise
>
> On Tue, Nov 18, 2014 at 12:50 PM, USMAN TARIQ
> wrote:
>
> Following is not woreking
>
>
>
> Private Sub Workbook_Open()
> Dim ws As Worksheet
>
> For Each ws In ThisWorkbook.Worksheets
> With ws
> .EnableOutlining = True
> .Protect UserInterfaceOnly:=True, AllowFiltering:=True, _
> AllowFormattingColumns:=True, AllowInsertingRows:=True
> End With
> NextEnd Sub
>
>
> On Tue, Nov 18, 2014 at 12:46 PM, USMAN TARIQ
> wrote:
>
> also i want i have 2 sheets in my work book, want to use the *Private Sub
> Workbook_Open() , its not working for me :(*
>
> On Tue, Nov 18, 2014 at 11:20 AM, USMAN TARIQ
> wrote:
>
> *is the following fine ?*
>
> Public Function FileFolderExists(strFullPath As String) As Boolean
> 'Author : Usman Tariq
> 'Macro Purpose: Check if a file or folder exists
> On Error GoTo EarlyExit
> If Not Dir(strFullPath, vbDirectory) = vbNullString Then
> FileFolderExists = True* And (DateDiff("d", f.datelastmodified, Now()) =
> 0)*
>
>
> EarlyExit:
> On Error GoTo 0
> End Function
>
>
>
>
>
>
>
> On Tue, Nov 18, 2014 at 11:18 AM, USMAN TARIQ
> wrote:
>
> following is the code in the *Module *
>
> Public Function FileFolderExists(strFullPath As String) As Boolean
> 'Author : Usman Tariq
> 'Macro Purpose: Check if a file or folder exists
> On Error GoTo EarlyExit
> If Not Dir(strFullPath, vbDirectory) = vbNullString Then
> FileFolderExists = True
>
> EarlyExit:
> On Error GoTo 0
> End Function
>
> Please advise where to put if *(datediff("d",f.**datelastmodified,now())
> = 0) then*
>
>
>
>
>
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegrou
Re: $$Excel-Macros$$ Error in VBA
What is not working?
Does it give an error?
You've got several things in this email.
which are you referring to?
You said:
i want i have 2 sheets in my work book, want to use the Private Sub
Workbook_Open() , its not working for me :(
the number of sheets has absolutely nothing to do with creating a Workbook_Open
event macro.
Unless you're trying to put the macro in a Sheet module instead of the
ThisWorkbook module.
please provide more information for what is happening (or not happening) and
what you WANT to happen.
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: USMAN TARIQ
>To: excel-macros@googlegroups.com
>Sent: Wednesday, November 19, 2014 10:39 AM
>Subject: Re: $$Excel-Macros$$ Error in VBA
>
>
>
>please advise
>
>
>On Tue, Nov 18, 2014 at 12:50 PM, USMAN TARIQ
>wrote:
>
>Following is not woreking
>>
>>
>>
>>
>>PrivateSubWorkbook_Open()Dimws AsWorksheet ForEachws
>>InThisWorkbook.Worksheets Withws .EnableOutlining =True.Protect
>>UserInterfaceOnly:=True,AllowFiltering:=True,_
>>AllowFormattingColumns:=True,AllowInsertingRows:=TrueEndWithNextEndSub
>>
>>
>>On Tue, Nov 18, 2014 at 12:46 PM, USMAN TARIQ
>>wrote:
>>
>>also i want i have 2 sheets in my work book, want to use the Private Sub
>>Workbook_Open() , its not working for me :(
>>>
>>>
>>>On Tue, Nov 18, 2014 at 11:20 AM, USMAN TARIQ
>>>wrote:
>>>
>>>is the following fine ?
Public Function FileFolderExists(strFullPath As String) As Boolean
'Author : Usman Tariq
'Macro Purpose: Check if a file or folder exists
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then
FileFolderExists = TrueAnd (DateDiff("d", f.datelastmodified, Now()) = 0)
EarlyExit:
On Error GoTo 0
End Function
On Tue, Nov 18, 2014 at 11:18 AM, USMAN TARIQ
wrote:
following is the code in the Module
>
>
>Public Function FileFolderExists(strFullPath As String) As Boolean
>'Author : Usman Tariq
>'Macro Purpose: Check if a file or folder exists
>On Error GoTo EarlyExit
>If Not Dir(strFullPath, vbDirectory) = vbNullString Then
> FileFolderExists = True
>
>EarlyExit:
>On Error GoTo 0
>End Function
>
>
>Please advise where to put if (datediff("d",f.datelastmodified,now()) = 0)
>then
>
>
>>>
>>
>
>--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
Re: $$Excel-Macros$$ Error in VBA
please advise
On Tue, Nov 18, 2014 at 12:50 PM, USMAN TARIQ
wrote:
> Following is not woreking
>
>
>
> Private Sub Workbook_Open()
> Dim ws As Worksheet
>
> For Each ws In ThisWorkbook.Worksheets
> With ws
> .EnableOutlining = True
> .Protect UserInterfaceOnly:=True, AllowFiltering:=True, _
> AllowFormattingColumns:=True, AllowInsertingRows:=True
> End With
> NextEnd Sub
>
>
> On Tue, Nov 18, 2014 at 12:46 PM, USMAN TARIQ
> wrote:
>
>> also i want i have 2 sheets in my work book, want to use the *Private
>> Sub Workbook_Open() , its not working for me :(*
>>
>> On Tue, Nov 18, 2014 at 11:20 AM, USMAN TARIQ
>> wrote:
>>
>>> *is the following fine ?*
>>>
>>> Public Function FileFolderExists(strFullPath As String) As Boolean
>>> 'Author : Usman Tariq
>>> 'Macro Purpose: Check if a file or folder exists
>>> On Error GoTo EarlyExit
>>> If Not Dir(strFullPath, vbDirectory) = vbNullString Then
>>> FileFolderExists = True* And (DateDiff("d", f.datelastmodified, Now())
>>> = 0)*
>>>
>>>
>>> EarlyExit:
>>> On Error GoTo 0
>>> End Function
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Tue, Nov 18, 2014 at 11:18 AM, USMAN TARIQ >> > wrote:
>>>
following is the code in the *Module *
Public Function FileFolderExists(strFullPath As String) As Boolean
'Author : Usman Tariq
'Macro Purpose: Check if a file or folder exists
On Error GoTo EarlyExit
If Not Dir(strFullPath, vbDirectory) = vbNullString Then
FileFolderExists = True
EarlyExit:
On Error GoTo 0
End Function
Please advise where to put if *(datediff("d",f.**datelastmodified,now())
= 0) then*
>>>
>>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Tandem filtering
Paul,
Thank you very much, for the detailed explanation.
Regards
Eugene
On Wed, Nov 19, 2014 at 6:27 PM, Paul Schreiner
wrote:
> In the "Orders" file, you have:
>
> ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2,
> Criteria1:=MYNUM
>
> Breaking this down:
> "ListObjects" is a property of the "Worksheet" Object.
> That means that it needs a Worksheet in order to operate.
>
> In your case, you've specified that you want the ListObjects property of
> the "Activesheet".
>
> "Activesheet" actually a short way of saying
> "the active sheet of the active workbook"
>
> Instead, you COULD have said something like:
>
> Workbooks("Orders.xls").Sheets(1)
> which specifies the first sheet of the workbook called "Orders.xls"
>
> or
> Workbooks("Orders.xls").Sheets("Sheet1")
> specifies the sheet called "Sheet1" in the workbook called "Orders.xls"
>
> So, if you specify a sheet in ANOTHER workbook, you can use something like:
>
> Workbooks("Orders.xls").Sheets("Sheet1").ListObjects("Table1").Range.AutoFilter
> _
>Field:=2, Criteria1:=MYNUM
> Workbooks("Stocks.xls").Sheets("Sheet1").ListObjects("Table1").Range.AutoFilter
> _
>Field:=1, Criteria1:=MYNUM
>
> (notice that your "ITEM" field in the "Stocks" workbook is column 1
> instead of 2)
>
> hope this helps
>
> *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:* Eugene Bernard
> *To:* excel-macros@googlegroups.com
> *Sent:* Tuesday, November 18, 2014 11:13 AM
> *Subject:* $$Excel-Macros$$ Tandem filtering
>
> Hi all,
>
> I have attached two workbooks, Orders and Stocks.
>
> In the Orders workbook, I have a macro, which filters Itemwise once I run
> the macro and enter the filter values like "A001", "A002" etc.,.
>
> I like to have a slight modifications in the same macro, so that, when I
> run the macro, filtering should happen simultaneously in the both
> workbooks "Orders" and "Stock"
>
> TIA
>
> Regards
> Eugene
> --
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
>
>--
> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES
>
> 1) Use concise, accurate thread titles. Poor thread titles, like Please
> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
> will not get quick attention or may not be answered.
> 2) Don't post a question in the thread of another member.
> 3) Don't post questions regarding breaking or bypassing any security
> measure.
> 4) Acknowledge the responses you receive, good or bad.
> 5) Jobs posting is not allowed.
> 6) Sharing copyrighted material and their links is not allowed.
>
> NOTE : Don't ever post confidential data in a workbook. Forum owners and
> members are not responsible for any loss.
> ---
> You received this message because you are subscribed to the Google Groups
> "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to excel-macros+unsubscr...@googlegroups.com.
> To post to this group, send email to excel-macros@googlegroups.com.
> Visit this group at http://groups.google.com/group/excel-macros.
> For more options, visit https://groups.google.com/d/optout.
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. P
Re: $$Excel-Macros$$ Tandem filtering
In the "Orders" file, you have:
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:=MYNUM
Breaking this down:
"ListObjects" is a property of the "Worksheet" Object.
That means that it needs a Worksheet in order to operate.
In your case, you've specified that you want the ListObjects property of
the "Activesheet".
"Activesheet" actually a short way of saying
"the active sheet of the active workbook"
Instead, you COULD have said something like:
Workbooks("Orders.xls").Sheets(1)
which specifies the first sheet of the workbook called "Orders.xls"
or
Workbooks("Orders.xls").Sheets("Sheet1")
specifies the sheet called "Sheet1" in the workbook called "Orders.xls"
So, if you specify a sheet in ANOTHER workbook, you can use something like:
Workbooks("Orders.xls").Sheets("Sheet1").ListObjects("Table1").Range.AutoFilter
_
Field:=2, Criteria1:=MYNUM
Workbooks("Stocks.xls").Sheets("Sheet1").ListObjects("Table1").Range.AutoFilter
_
Field:=1, Criteria1:=MYNUM
(notice that your "ITEM" field in the "Stocks" workbook is column 1 instead of
2)
hope this helps
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: Eugene Bernard
>To: excel-macros@googlegroups.com
>Sent: Tuesday, November 18, 2014 11:13 AM
>Subject: $$Excel-Macros$$ Tandem filtering
>
>
>
>Hi all,
>
>I have attached two workbooks, Orders and Stocks.
>
>In the Orders workbook, I have a macro, which filters Itemwise once I run the
>macro and enter the filter values like "A001", "A002" etc.,.
>
>
>I like to have a slight modifications in the same macro, so that, when I run
>the macro, filtering should happen simultaneously in the both workbooks
>"Orders" and "Stock"
>
>
>TIA
>
>Regards
>Eugene
>
--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Add and remove text box on ever time button click.
Your userform currently has (3) rows of(5) textboxes. Are you saying that when you hit "Add" you want to add another row of (5) textboxes? And when you hit "Remove", it should remove the row of (5) boxes? If you remove the second row, what do you want to have happen to the rest? (third row move up to row 2 or ??) Is there a limit to the number of rows? what happens when you exceed the size of the userform? do you want the macro to increase the size of the userform? 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: Rupesh Patil >To: excel-macros@googlegroups.com >Sent: Wednesday, November 19, 2014 3:24 AM >Subject: Re: $$Excel-Macros$$ Add and remove text box on ever time button >click. > > > >Please Experts help me out. > > >On Tue, Nov 18, 2014 at 5:07 PM, Rupesh Patil >wrote: > >Hello Experts, >> >>any help? >> >> >> >> >>On Mon, Nov 17, 2014 at 4:10 PM, Rupesh Patil >>wrote: >> >>Hello experts, >>> >>> >>>I need your help,I have one userform which attached with mail for your >>>reference, what I need is add text boxes vertical on button every time click >>>"Add Button" and remove this text boxes every time on "Remove Button" click. >>>and Add-Remove row accordingly in sheet. >>> >>>-- >>> >>>Thanks & Regards, >>>Rupesh Patil, >>>(9970196189). -- >>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >>>https://www.facebook.com/discussexcel >>> >>>FORUM RULES >>> >>>1) Use concise, accurate thread titles. Poor thread titles, like Please >>>Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will >>>not get quick attention or may not be answered. >>>2) Don't post a question in the thread of another member. >>>3) Don't post questions regarding breaking or bypassing any security measure. >>>4) Acknowledge the responses you receive, good or bad. >>>5) Jobs posting is not allowed. >>>6) Sharing copyrighted material and their links is not allowed. >>> >>>NOTE : Don't ever post confidential data in a workbook. Forum owners and >>>members are not responsible for any loss. >>>--- >>>You received this message because you are subscribed to the Google Groups >>>"MS EXCEL AND VBA MACROS" group. >>>To unsubscribe from this group and stop receiving emails from it, send an >>>email to excel-macros+unsubscr...@googlegroups.com. >>>To post to this group, send email to excel-macros@googlegroups.com. >>>Visit this group at http://groups.google.com/group/excel-macros. >>>For more options, visit https://groups.google.com/d/optout. >>> >> >> >> >>-- >> >>Thanks & Regards, >>Rupesh Patil, >>(9970196189), >>Ayushman Pvt. Ltd. >> >> > > > >-- > >Thanks & Regards, >Rupesh Patil, >(9970196189), >Ayushman Pvt. Ltd. > > -- >Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES > >1) Use concise, accurate thread titles. Poor thread titles, like Please Help, >Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get >quick attention or may not be answered. >2) Don't post a question in the thread of another member. >3) Don't post questions regarding breaking or bypassing any security measure. >4) Acknowledge the responses you receive, good or bad. >5) Jobs posting is not allowed. >6) Sharing copyrighted material and their links is not allowed. > >NOTE : Don't ever post confidential data in a workbook. Forum owners and >members are not responsible for any loss. >--- >You received this message because you are subscribed to the Google Groups "MS >EXCEL AND VBA MACROS" group. >To unsubscribe from this group and stop receiving emails from it, send an >email to excel-macros+unsubscr...@googlegroups.com. >To post to this group, send email to excel-macros@googlegroups.com. >Visit this group at http://groups.google.com/group/excel-macros. >For more options, visit https://groups.google.com/d/optout. > > > -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted materi
$$Excel-Macros$$ Excel Question
Hi all! Here I got a question on the another community. He wants to achieve maximum in other column for example first 7 in between 3,2,6,7 and then next range max 8 in between 8,2,3 . I have prepared the formula for this but want to know some more suggestions. :) Que : - I have some numbers zeros and non-zeros in Column A and would like to see the maximum number flanked by zeros in Column A's non-zero range in Column B. What formula should be written in cells in Column B? A B 0 0 0 0 3 7 2 7 6 7 7 7 0 0 0 0 0 0 8 8 2 8 3 8 0 0 Regards *Mandeep Baluja * -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
Re: $$Excel-Macros$$ Add and remove text box on ever time button click.
Please Experts help me out. On Tue, Nov 18, 2014 at 5:07 PM, Rupesh Patil wrote: > Hello Experts, > > any help? > > > On Mon, Nov 17, 2014 at 4:10 PM, Rupesh Patil > wrote: > >> Hello experts, >> >> I need your help,I have one userform which attached with mail for your >> reference, what I need is add text boxes vertical on button every time >> click "Add Button" and remove this text boxes every time on "Remove Button" >> click. and Add-Remove row accordingly in sheet. >> >> -- >> Thanks & Regards, >> Rupesh Patil, >> (9970196189) >> . >> >> -- >> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s >> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES >> >> 1) Use concise, accurate thread titles. Poor thread titles, like Please >> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice >> will not get quick attention or may not be answered. >> 2) Don't post a question in the thread of another member. >> 3) Don't post questions regarding breaking or bypassing any security >> measure. >> 4) Acknowledge the responses you receive, good or bad. >> 5) Jobs posting is not allowed. >> 6) Sharing copyrighted material and their links is not allowed. >> >> NOTE : Don't ever post confidential data in a workbook. Forum owners and >> members are not responsible for any loss. >> --- >> You received this message because you are subscribed to the Google Groups >> "MS EXCEL AND VBA MACROS" group. >> To unsubscribe from this group and stop receiving emails from it, send an >> email to excel-macros+unsubscr...@googlegroups.com. >> To post to this group, send email to excel-macros@googlegroups.com. >> Visit this group at http://groups.google.com/group/excel-macros. >> For more options, visit https://groups.google.com/d/optout. >> > > > > -- > Thanks & Regards, > Rupesh Patil, > (9970196189), > Ayushman Pvt. Ltd. > > -- Thanks & Regards, Rupesh Patil, (9970196189), Ayushman Pvt. Ltd. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
