Re: $$Excel-Macros$$ Highlight annual leave schedule of each staff for easy comparison
Dear Paul Thanks for the support. Yours is an excellent attendance tracker, which is much more than what I needed. Can I have a simple, single sheet vacation planner, similar to the one I have attached ? On Wed, Oct 24, 2012 at 9:19 PM, Paul Schreiner wrote: > YahYa, > > Here is a scrubbed version of an application I wrote several years ago. > > In this case, individual Attendance is recorded on separate sheets > I used User Names: > Micky Dolenz > Michael Nesmith > Peter Tork > Davy Jones > > For each user, the sheet shows their "Allowed" Vacation days. > We are allowed to schedule our "vacation" in 1 hour increments. > When recording future vacation the number of hours is followed by a "V". > Therefore, if I'm scheduled for a full day vacation on Friday, > I place "8V" in the cell for that date. > Once that time has actually been "taken", value is changed to 8. > > If, for some reason, something comes up and an employee must take a > vacation > day that is not scheduled, the hours (usually 8) is placed in the date > actually taken. > A warning message will be displayed indicating that more vacation is > scheduled > than allowed. The user must then remove one of the other scheduled days. > > In this application, absenses due to "illness" is also tracked by using > 8I to indicate a "sick day". > > On the Summary sheet, the button is used to read from the other sheets > and indicate the number of employees scheduled, (or have already taken) > vacation > time on a given day. > > A Comment is created, referring to the users that are scheduled for these > days. > > Let me know if this is helpful. > > > *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:* Paul Schreiner > *To:* excel-macros@googlegroups.com > *Sent:* Wed, October 24, 2012 7:49:35 AM > *Subject:* Re: $$Excel-Macros$$ Highlight annual leave schedule of each > staff for easy comparison > > That's a start... but it doesn't have all of the days of the year. > (you probably noticed that) > > Several years ago (10+?) I put together an attendance tracker for my > supervisor. > There were separate sheets for each individual in the department, > then a summary sheet that displayed how many people were scheduled to be > absent > on any given day. > It also kept track of days missed due to illness, or other. > In addition, each employee in the department was eligible for a different > number > of days of vacation per year, depending on their years of service. > This kept track of vacation days scheduled as well as days actually taken. > We also allow 1/2 day vacation, which it also kept track of. > > I'll try to get an updated copy, scrub it to remove personal info, and > post it. > > > *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:* Yahya Muhammad > *To:* excel-macros@googlegroups.com > *Sent:* Wed, October 24, 2012 3:19:47 AM > *Subject:* $$Excel-Macros$$ Highlight annual leave schedule of each staff > for easy comparison > > Hi experts > > I want to compile the annual leave schedule of each staff in the > department in excel. The idea is to have all the schedule in one work > sheet, for easy comparison and to know the leave overlapping,if any, at one > glance. Currently I am using the attached sheet, which I think, can be > modified/automated to have a better form. Awaiting your help please. > > -- > Join official facebook page of this forum @ > https://www.facebook.com/discussexcel > > FORUM RULES (1120+ members already BANNED for violation) > > 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) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > 6) Jobs posting is not allowed. > > 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. > > NOTE : Don't ever post personal or 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 post to this group, send email
RE: $$Excel-Macros$$ Re: Help need to get multiple values by vlookup
Hi Aamir,
Not sure exactly what you're after (haven't seen your workbook), but perhaps
this is helpful:
{
=TRANSPOSE(INDEX(TRANSPOSE(ColumnsOfTableYouWantReturned),,MATCH(WhatValueTo
Lookup,LookupRange,0))) }
If your values to return are all numbers, you can use this instead:
{
=INDEX(--ColumnsOfTableYouWantReturned,MATCH(WhatValueToLookup,LookupRange,0
)) }
If your values to return are all text, you can use this one instead:
{
=INDEX(ColumnsOfTableYouWantReturned&"",MATCH(WhatValueToLookup,LookupRange,
0)) }
The reason for the TRANSPOSEs, -- and &"" in each of the above solutions is
to convert the lookup table to an array. Index can return an entire row or
column at once from an array, but not from a range.
Enter the above array formulas (between the braces) over several columns and
confirm entry with ctrl-shift-enter. It will essentially return the whole
matching record or as many columns of it as you specify. If LookupRange is
sorted, you may want to change the ,0 near the end of the formula to 1 (for
ascending order) or -1 (for descending order). The upside to that change is
it will make the lookup very fast. The downside is that if there is not an
exact match, it will return a neighboring record instead.
The above formulas will return the same values that a series of VLOOKUPs in
each column would achieve, but faster.
Sincerely,
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Aamir Shahzad
Sent: Wednesday, October 24, 2012 11:10 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Re: Help need to get multiple values by
vlookup
I have registered thanks as well
On Thu, Oct 25, 2012 at 11:06 AM, ashish damor
wrote:
Dear Aamir,
The registration is free and it does not take even 5 minutes to register.
On Thu, Oct 25, 2012 at 10:52 AM, Aamir Shahzad
wrote:
> Dear Rajan,
>
> If you have file plz share with us, your required link want to register
> first.
>
>
>
> On Thu, Oct 25, 2012 at 8:23 AM, Rajan_Verma
> wrote:
>>
>> Hi
>>
>> Download the file from here
>>
>> http://www.excelfox.com/forum/f12/vlookup-return-multiple-values-4/
>>
>> Thanks
>> Rajan.
>>
>> On Wednesday, 24 October 2012 22:51:01 UTC+5:30, saravanan R wrote:
>>>
>>> Hi xlts,
>>>
>>> Please help me, on how to get the all the associated items In the range
>>> where it as more than 1 lookupvalue.
>>>
>>> Thanks,
>>> Saravanan
>>
>> --
>> Join official facebook page of this forum @
>> https://www.facebook.com/discussexcel
>>
>> FORUM RULES (1120+ members already BANNED for violation)
>>
>> 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) Cross-promotion of, or links to, forums competitive to this forum in
>> signatures are prohibited.
>>
>> 6) Jobs posting is not allowed.
>>
>> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>>
>> NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com.
>> To unsubscribe from this group, send email to
>> excel-macros+unsubscr...@googlegroups.com
.
>>
>>
>
>
>
>
> --
>
> Regards,
>
> Aamir Shahzad
>
> --
> Join official facebook page of this forum @
> https://www.facebook.com/discussexcel
>
> FORUM RULES (1120+ members already BANNED for violation)
>
> 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) Cross-promotion of, or links to, forums competitive to this forum in
> signatures are prohibited.
>
> 6) Jobs posting is not allowed.
>
> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
>
> NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com.
> To unsubscribe from this group, send email to
> excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Highlight annual leave schedule of each staff for easy comparison
Are you saying you want me to design something like what you already have?? If so.. I'd need your requirements.. and the time to do it. 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: Yahya Muhammad To: excel-macros@googlegroups.com Sent: Thu, October 25, 2012 4:00:10 AM Subject: Re: $$Excel-Macros$$ Highlight annual leave schedule of each staff for easy comparison Dear Paul Thanks for the support. Yours is an excellent attendance tracker, which is much more than what I needed. Can I have a simple, single sheet vacation planner, similar to the one I have attached ? On Wed, Oct 24, 2012 at 9:19 PM, Paul Schreiner wrote: YahYa, > >Here is a scrubbed version of an application I wrote several years ago. > >In this case, individual Attendance is recorded on separate sheets >I used User Names: > Micky Dolenz > Michael Nesmith > Peter Tork > Davy Jones > >For each user, the sheet shows their "Allowed" Vacation days. >We are allowed to schedule our "vacation" in 1 hour increments. >When recording future vacation the number of hours is followed by a "V". >Therefore, if I'm scheduled for a full day vacation on Friday, >I place "8V" in the cell for that date. >Once that time has actually been "taken", value is changed to 8. > >If, for some reason, something comes up and an employee must take a vacation >day that is not scheduled, the hours (usually 8) is placed in the date >actually taken. >A warning message will be displayed indicating that more vacation is scheduled >than allowed. The user must then remove one of the other scheduled days. > >In this application, absenses due to "illness" is also tracked by using >8I to indicate a "sick day". > >On the Summary sheet, the button is used to read from the other sheets >and indicate the number of employees scheduled, (or have already taken) vacation >time on a given day. > >A Comment is created, referring to the users that are scheduled for these days. > >Let me know if this is helpful. > >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: Paul Schreiner >To: excel-macros@googlegroups.com >Sent: Wed, October 24, 2012 7:49:35 AM >Subject: Re: $$Excel-Macros$$ Highlight annual leave schedule of each staff >for >easy comparison > > > >That's a start... but it doesn't have all of the days of the year. >(you probably noticed that) > >Several years ago (10+?) I put together an attendance tracker for my supervisor. >There were separate sheets for each individual in the department, >then a summary sheet that displayed how many people were scheduled to be absent >on any given day. >It also kept track of days missed due to illness, or other. >In addition, each employee in the department was eligible for a different number >of days of vacation per year, depending on their years of service. >This kept track of vacation days scheduled as well as days actually taken. >We also allow 1/2 day vacation, which it also kept track of. > >I'll try to get an updated copy, scrub it to remove personal info, and post it. > >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: Yahya Muhammad >To: excel-macros@googlegroups.com >Sent: Wed, October 24, 2012 3:19:47 AM >Subject: $$Excel-Macros$$ Highlight annual leave schedule of each staff for >easy >comparison > > >Hi experts > > >I want to compile the annual leave schedule of each staff in the department in >excel. The idea is to have all the schedule in one work sheet, for easy >comparison and to know the leave overlapping,if any, at one glance. Currently >I >am using the attached sheet, which I think, can be modified/automated to have >a >better form. Awaiting your help please.-- > >Join official facebook page of this forum @ >https://www.facebook.com/discussexcel > >FORUM RULES (1120+ members already BANNED for violation) > >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 me
$$Excel-Macros$$ By Default Highlight the row based on the location of the cursor like the attached excel
Dear Gurus, Please help me to highlight the row based on the location of the cursor. This should be applied default to all the excel file im opening. Thanks N.Sundarvelan 9600160150 -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. ROW_HIGHLIGHT_MACRO_V1.xlsm Description: Binary data
Re: $$Excel-Macros$$ By Default Highlight the row based on the location of the cursor like the attached excel
This looks very similar to the topic you posted called: Macro Help: Repeat the macro for each cursor move The same answers apply. You can create an event macro that clears all highlighting and highlights the row and/or column of the selected cell. As for your requirement: "This should be applied default to all the excel file im opening" I don't think that's possible. Event macros are tied to the Workbook (file) in which they reside. You can make the macro run for each SHEET in the workbook by adding this macro to the 'ThisWorkbook' module: Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) With Cells.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With With Selection.EntireRow.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With End Sub If you want the entire row AND COLUMN to highlight, add: With Selection.EntireColumn.Interior .Pattern = xlSolid .PatternColorIndex = xlAutomatic .Color = 65535 .TintAndShade = 0 .PatternTintAndShade = 0 End With 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: Sundarvelan N To: excel-macros@googlegroups.com Sent: Thu, October 25, 2012 8:00:16 AM Subject: $$Excel-Macros$$ By Default Highlight the row based on the location of the cursor like the attached excel Dear Gurus, Please help me to highlight the row based on the location of the cursor. This should be applied default to all the excel file im opening. ThanksN.Sundarvelan 9600160150 -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
Re: $$Excel-Macros$$ Google Map and Excel Integration Using VBA
Two more Plot locations on Google map by passing Latitude and Longitude Using VBA and Import Map to Excel http://excelvbaprogramming.wordpress.com/2012/10/25/plot-locations-on-google-map-by-passing-latitude-and-longitude-using-vba-and-import-map-to-excel/ Find Distance between two places by passing Latitude and Longitude of Origin and Destination To Google Map http://excelvbaprogramming.wordpress.com/2012/10/25/find-distance-between-two-places-by-passing-latitude-and-longitude-of-origin-and-destination/ Regards Ashish On Sat, Oct 20, 2012 at 12:19 PM, Ashish Bhalara wrote: > THank you very much Mr. Ashish Koul, its very useful and interesting files. > > > On Fri, Oct 19, 2012 at 7:53 PM, Sam Mathai Chacko wrote: > >> This is very good Ashish. Thanks for sharing. >> >> Regards, >> Sam >> >> On Fri, Oct 19, 2012 at 7:20 PM, ashish koul wrote: >> >>> Hi All, >>> >>> If you want to get latitude and longitude of any address or you want >>> to find the distance between two cities , etc using Google Map. Please find >>> below links of macros which will automatically fetch or get data from >>> Google Maps using VBA >>> >>> >>> Find Distance and Time between two cities using Google API In excel >>> >>> http://excelvbaprogramming.wordpress.com/2012/09/24/find-distance-and-time-between-two-cities-using-google-api-in-excel/ >>> >>> >>> Display Google Map in Excel using VBA and Google API >>> >>> http://excelvbaprogramming.wordpress.com/2012/09/24/display-google-map-in-excel-using-vba-and-google-api/ >>> >>> >>> Plot locations on Google map using VBA and Import updated map to Excel >>> >>> http://excelvbaprogramming.wordpress.com/2012/09/26/plot-locations-on-google-map-using-vba-and-import-updated-map-to-excel/ >>> >>> >>> Plot Origin and destination city on Google Map and import Map to Excel >>> >>> http://excelvbaprogramming.wordpress.com/2012/09/27/plot-origin-and-destination-city-on-google-map-and-import-map-to-excel/ >>> >>> Google Translation Using VBA >>> >>> http://excelvbaprogramming.wordpress.com/2012/09/29/google-translation-using-vba/ >>> >>> Find Latitude and Longitude of any address using Google Map API and VBA >>> >>> http://excelvbaprogramming.wordpress.com/2012/10/18/find-latitude-and-longitude-of-any-address-using-google-map-api-and-vba/ >>> >>> -- >>> *Regards* >>> * * >>> *Ashish Koul* >>> >>> *Visit* >>> *http://www.excelvbamacros.com/* >>> *http://www.accessvbamacros.com/* >>> >>> P Before printing, think about the environment. >>> >>> >>> >>> -- >>> Join official facebook page of this forum @ >>> https://www.facebook.com/discussexcel >>> >>> FORUM RULES (1120+ members already BANNED for violation) >>> >>> 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) Cross-promotion of, or links to, forums competitive to this forum in >>> signatures are prohibited. >>> >>> 6) Jobs posting is not allowed. >>> >>> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. >>> >>> NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com. >>> To unsubscribe from this group, send email to >>> excel-macros+unsubscr...@googlegroups.com. >>> >>> >>> >> >> >> >> -- >> Sam Mathai Chacko >> >> -- >> Join official facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES (1120+ members already BANNED for violation) >> >> 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) Cross-promotion of, or links to, forums competitive to this forum in >> signatures are prohibited. >> >> 6) Jobs posting is not allowed. >> >> 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. >> >> NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com. >> To unsubscribe from this group, send email to >> excel
Re: $$Excel-Macros$$ Masking Password in input box * *
MYpassword =frm_pwd.Show
won't work.
Think about UserForms for a minute.
you can have numerous text boxes, list boxes, labels and buttons.
When you close the form,
WHAT gets "returned" to the calling program?
In fact, I don't think the .Show method returns ANYTHING.
You might be able to create a UserForm_Terminate event macro
that would set Mypassword = frm_pwd.(name of password box)
That might be the best way to handle it with the userform.
Or make a button on the form.
Or even a change event for the password box on the form.
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: Hilary Lomotey
To: excel-macros@googlegroups.com
Sent: Thu, October 25, 2012 12:49:11 PM
Subject: Re: $$Excel-Macros$$ Masking Password in input box * *
Thanks Ashish, really grateful for your assistance
but the problem is my vba is still fundamental , i tried linking what u sent to
me to the code below but i getting an error. could you amend the code for me.
thanks
i substitued MYpassword = InputBox("Enter Password") line with
MYpassword = frm_pwd.Show
Private Sub Worksheet_Activate()
Static PasswordCorrect As Boolean
Dim MYpassword As String
If PasswordCorrect = False Then
Sheets("Sheet1").Select 'here We Move to Sheet1 (Or whatever sheet you
want, just change the name)
ElseIf PasswordCorrect = True Then
Exit Sub
End If
MYpassword = InputBox("Enter Password")
If MYpassword = "nwiadmin01'" Then 'excelpros01'
PasswordCorrect = True
Sheets("Sheet2").Select 'Put the name of your protected sheet here
Else
Sheets("Sheet1").Select
PasswordCorrect = False
End If
End Sub
On Thu, Oct 25, 2012 at 3:33 PM, ashish koul wrote:
This message is eligible for Automatic Cleanup! (koul.ash...@gmail.com) Add
cleanup rule | More info
>
>see the attached file and make the changes as per your requirement
>
>
>On Wed, Oct 24, 2012 at 11:04 PM, Hilary Lomotey wrote:
>
>Hello Again,
>>
>>
>>Pls how to i combine the code below here to the code in this attached
>>workbook
>>(got it on the web) to achieve my desired results. Desired results being
>>being
>>able to password protect a sheet worksheet (just as the code below is doing
>>only
>>that its not masking the password).
>>The attached workbook contains a password userform
>>
>>
>>i dont know how to combine these codes to achieve my objective. pls assist
>>
>>
>>
>>
>>
>>
>>
>>
>>Private Sub Worksheet_Activate()
>> Static PasswordCorrect As Boolean
>> Dim MYpassword As String
>> If PasswordCorrect = False Then
>> Sheets("Sheet1").Select 'here We Move to Sheet1 (Or whatever sheet
>>you
>>want, just change the name)
>> ElseIf PasswordCorrect = True Then
>> Exit Sub
>> End If
>>
>> MYpassword = InputBox("Enter Password")
>>
>> If MYpassword = "123'" Then
>> PasswordCorrect = True
>> Sheets("Sheet2").Select 'Put the name of your protected sheet here
>> Else
>> Sheets("Sheet1").Select
>> PasswordCorrect = False
>> End If
>>End Sub
>>
>>
>>On Wed, Oct 24, 2012 at 4:52 PM, Hilary Lomotey wrote:
>>
>>Hello
>>>
>>>
>>>Pls see attatched file as example
>>>
>>>
>>>
>>>On Wed, Oct 24, 2012 at 4:45 PM, Hilary Lomotey wrote:
>>>
>>>Thanks Ashish,
one question do i paste the same code in the userform? how do i go about it
>>pls
On Wed, Oct 24, 2012 at 3:17 PM, ashish koul wrote:
This message is eligible for Automatic Cleanup! (koul.ash...@gmail.com)
Add
cleanup rule | More info
>
>use userform and textbox instead of msgbox and in text box choose * in
>passwordchar
>
>
>
>
>
>
>
>
>
>
>On Wed, Oct 24, 2012 at 8:44 PM, Hilary Lomotey wrote:
>
>Hi Exxperts
>>
>>
>> I got this code to protect a worksheet, which was exactly what i was
>>looking
>>for only that entering the password into the input box is unmasked so
>>everyone
>>standing by can see your password.
>>
>>
>>can i get a code to mask the password input?. or better still another
>>code to
>>ask for password (masked) when a particular worksheet is acessed. thanks
>>
>>
>>
>>Private Sub Worksheet_Activate()
>> Static PasswordCorrect As Boolean
>> Dim MYpassword As String
>> If PasswordCorrect = False Then
>> Sheets("Sheet1").Select 'here We Move to Sheet1 (Or whatever
>>sheet you
>>want, just change the name)
>> ElseIf PasswordCorrect = True Then
>> Exit Sub
>> End If
>>
>> MYpassword = InputBox("Enter Password")
>>
>>
Re: $$Excel-Macros$$ Highlight annual leave schedule of each staff for easy comparison
Yes Paul. You can do it on your free time please. On Thu, Oct 25, 2012 at 2:15 PM, Paul Schreiner wrote: > Are you saying you want me to design something like what you already have?? > If so.. I'd need your requirements.. and the time to do it. > > > *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:* Yahya Muhammad > *To:* excel-macros@googlegroups.com > *Sent:* Thu, October 25, 2012 4:00:10 AM > > *Subject:* Re: $$Excel-Macros$$ Highlight annual leave schedule of each > staff for easy comparison > > Dear Paul > > Thanks for the support. Yours is an excellent attendance tracker, which is > much more than what I needed. Can I have a simple, single sheet vacation > planner, similar to the one I have attached ? > > > On Wed, Oct 24, 2012 at 9:19 PM, Paul Schreiner wrote: > >> YahYa, >> >> Here is a scrubbed version of an application I wrote several years ago. >> >> In this case, individual Attendance is recorded on separate sheets >> I used User Names: >> Micky Dolenz >> Michael Nesmith >> Peter Tork >> Davy Jones >> >> For each user, the sheet shows their "Allowed" Vacation days. >> We are allowed to schedule our "vacation" in 1 hour increments. >> When recording future vacation the number of hours is followed by a "V". >> Therefore, if I'm scheduled for a full day vacation on Friday, >> I place "8V" in the cell for that date. >> Once that time has actually been "taken", value is changed to 8. >> >> If, for some reason, something comes up and an employee must take a >> vacation >> day that is not scheduled, the hours (usually 8) is placed in the date >> actually taken. >> A warning message will be displayed indicating that more vacation is >> scheduled >> than allowed. The user must then remove one of the other scheduled days. >> >> In this application, absenses due to "illness" is also tracked by using >> 8I to indicate a "sick day". >> >> On the Summary sheet, the button is used to read from the other sheets >> and indicate the number of employees scheduled, (or have already taken) >> vacation >> time on a given day. >> >> A Comment is created, referring to the users that are scheduled for these >> days. >> >> Let me know if this is helpful. >> >> >> *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:* Paul Schreiner >> *To:* excel-macros@googlegroups.com >> *Sent:* Wed, October 24, 2012 7:49:35 AM >> *Subject:* Re: $$Excel-Macros$$ Highlight annual leave schedule of each >> staff for easy comparison >> >> That's a start... but it doesn't have all of the days of the year. >> (you probably noticed that) >> >> Several years ago (10+?) I put together an attendance tracker for my >> supervisor. >> There were separate sheets for each individual in the department, >> then a summary sheet that displayed how many people were scheduled to be >> absent >> on any given day. >> It also kept track of days missed due to illness, or other. >> In addition, each employee in the department was eligible for a different >> number >> of days of vacation per year, depending on their years of service. >> This kept track of vacation days scheduled as well as days actually taken. >> We also allow 1/2 day vacation, which it also kept track of. >> >> I'll try to get an updated copy, scrub it to remove personal info, and >> post it. >> >> >> *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:* Yahya Muhammad >> *To:* excel-macros@googlegroups.com >> *Sent:* Wed, October 24, 2012 3:19:47 AM >> *Subject:* $$Excel-Macros$$ Highlight annual leave schedule of each >> staff for easy comparison >> >> Hi experts >> >> I want to compile the annual leave schedule of each staff in the >> department in excel. The idea is to have all the schedule in one work >> sheet, for easy comparison and to know the leave overlapping,if any, at one >> glance. Currently I am using the attached sheet, which I think, can be >> modified/automated to have a better form. Awaiting your help please. >> >> -- >> Join official facebook page of this forum @ >> https://www.facebook.com/discussexcel >> >> FORUM RULES (1120+ members
$$Excel-Macros$$ Help needed to write For/End loop using Range statement ...
Hi,
I would like your help in writing a simple macro that will perform a
"For/End" loop around a ".Range" statement. I would like the cells in the
Range statement to increment by 1 for X number of times. My problem is I do
not know how to change the code so that the cells are incremented by the do
loop. For example the following code places content in cells AP1 and AP2:
With ActiveSheet
.Range("AP1").FormulaR1C1 = "'Preferred_Address_Indicator"
.Range("AP2").Formula = "=Switch2(Y2,""B"",
AH2,""H"",NOT(AH2),""O"")"
End With
How do I replace the AP1 and AP2 so that it uses a do loop?
Thanks in advance for your assistance.
-Greg
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
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) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Re: Nice Chart
My internet not working these days. will come back to you shortly :) On Thursday, 25 October 2012 10:34:33 UTC+5:30, vijay wrote: > > Dear Rajan, > > Excellent work buddy.Can you please mention the steps required to create a > chart like that?Does it work with Excel 2007? > > > > Regards. > P.VIJAY KUMAR > -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Re: Help needed to write For/End loop using Range statement ...
Hi
you can visit here to learn all loop in vba
http://excelpoweruser.wordpress.com/2011/07/26/looping-in-vba/
Thanks
Rajan.
On Friday, 26 October 2012 02:26:27 UTC+5:30, Greg wrote:
>
> Hi,
>
> I would like your help in writing a simple macro that will perform a
> "For/End" loop around a ".Range" statement. I would like the cells in the
> Range statement to increment by 1 for X number of times. My problem is I do
> not know how to change the code so that the cells are incremented by the do
> loop. For example the following code places content in cells AP1 and AP2:
>
> With ActiveSheet
> .Range("AP1").FormulaR1C1 = "'Preferred_Address_Indicator"
> .Range("AP2").Formula = "=Switch2(Y2,""B"",
> AH2,""H"",NOT(AH2),""O"")"
> End With
>
> How do I replace the AP1 and AP2 so that it uses a do loop?
>
> Thanks in advance for your assistance.
>
> -Greg
>
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
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) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Re: A first sheet to be opened always in a excel file
Hi Pravin,
Yes it is possible just copy ansd paste the following code on workbook open.
Private Sub Workbook_Open()
ThisWorkbook.Worksheets("test").Activate
End Sub
Note: just pass your sheet name instead of test
Regards
Prince.
On Friday, October 26, 2012 10:16:16 AM UTC+5:30, Pravin Gunjal wrote:
>
> *Dear Friends,*
> *
> *
> *Greetings!*
> *
> *
> *As usual we do a work in any of the sheets of an Excel and close the
> file as it is after saving. **And I am having an excel file containing
> 5-6 sheets in it and I wants to open only the first sheet always whenever I
> open that file.*
> *
> *
> *Is it possible to open always a particular sheet (sheet no.1) while we
> open an excel file.*
>
> *Thanks,*
> *Pravin Gunjal.*
> *
> *
>
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
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) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Re: Chart Stuff
Excellent work dear my friend. Regards Prince On Wednesday, October 24, 2012 6:35:34 PM UTC+5:30, Rajan_Verma wrote: > > * * > > *Hi Group* > > *I am just sharing my chart stuff with you, hope you would like it * > > * * > > > > * * > > *Comments are welcome **J*** > > * * > > *Regards* > > *Rajan verma* > > *+91 7838100659 [IM-Gtalk]* > > > -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com.
$$Excel-Macros$$ Need a Function on Multiple drop down list
Hi All, Firstly let me thanks to the admin and all the members for their wonderful job which they are contributing in this great forum. I have learnt a lot from all of you. I need a function on drop down list. Actually, i want to create a order loss sheet, i have already made drop down list for categories & their sub categories, now, if i select category then products of the category must be highlighted in selected cell, and in next cell rate of the products will show. All the function need to be interlinked itself with product category-product-rate. Sheet attached for your information. Do the needful, Thanks Rahul Sharma -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Booking & Order loss Sheet.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Need sale report combine with "Date", "XX" & "Airtel" from next sheet'
Dear Expert, Please help me to get the currect ans with attached file.. Regard Manoj -- Join official facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES (1120+ members already BANNED for violation) 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) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. 6) Jobs posting is not allowed. 7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed. NOTE : Don't ever post personal or 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 post to this group, send email to excel-macros@googlegroups.com. To unsubscribe from this group, send email to excel-macros+unsubscr...@googlegroups.com. Dummy.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
