Posts by Sumit Dey

Hi,

1. Workbooks are named Prt_Oct 2019 and Prt_Nov 2019.

2. both books will be open (suggest if it will work when one remain closed).

3. Prt_Nov 2019 should have the results in it.

4. Yes the first row have headers.

5. 1st col have the unique identifier ( Col header named ISIN).

6. Col E will have Returns.

7. Columns headers are consistent.

Thanks

Hi All Good Evening,

I have came across an problem to which I am unable to find solution need your expertise to help me out.

I have two workbooks with around 60-70 tabs each (tabs are named similar in both the workbooks), I need to find difference between two tabs from both the workbooks ( which have same name).

Say both the workbooks have a Tab (Sheet) named "FTSE100" and have certain data in them. I want to know the difference between them. Both the sheets have a particular column named "Returns" I have to find the difference between them. It also have another column with unique identifier so that I can apply Vlookup to get the desired results.

But, the issue is there are more than 60 tabs (sheets) in each workbook and it will take a hell lot of time to apply Vlookup in each of the tabs (sheets).

I need that a VBA code creates a new sheet and shows results for each of the Sheets between both the workbook.

I hope I have clearly mentioned what I am looking for here.??

Hi everyone,

I know how to send emails to several emails ID's at a time using VBA... But, I am looking to send different emails to each email ID/ID's at once. Like we do in a mail merge.

Currently I am using mail merge whereas, some sections of the email are fixed and are taken from an word file but, some other parts of the email had to be different and here we use the data from an excel file. We merge data from the word file and the excel file and send them all at once.

I would be thankful if someone can find an alternate way of doing the same using the Excel vba alone.

Thanks a lot in advance

Hello everyone,

I am back with another query in excel. This may be easier for you all but, for me it's like climbing a mountain.

I have a excel sheet which have start time and end time columns and a touch time column which calculate the total time simple. But, I have another column named MyTime and I want the answer to be the figure from touchtime column converted into number format and divided by 60 and again the answer should be in h:mm format.
e.g, say touchtime column have 1.15 hrs converted into number should be 75 mins and this number should be again divided by 60 and format as h:mm so, that the answer comes to 1.25 hrs.
I have attached an example sheet for your references.

Thanks everyone for your help in advance.

Re: Open a html file in under Workbook_open () event on a particular day only

Hey John thanks a lot that's exactly working the way I expected...thumbs up to you.

At times we may have to submit MIS on other days as well say if we have a holiday on friday then we have to submit it on Thursday. Is there any possibility to make a dynamic code that works this way????

Hi All,

I am here again with another query.

I want to inform my colleagues that they should submit their MIS data each friday and for this I want to inform they by way of an animated html file which shall open when they open their respective MIS (in excel).
I am using the below code:

Private Sub Workbook_Open()
ActiveWorkbook.Followhyperlink"c:\mypathhere\ABC.html
End Sub

This is working ok but, what I want to do here is that the html file shall only open each friday and not everyday also please advice if there's any other alternate way to do it.

Thanks in advance for your support

Re: Add 1 each time a button is clicked on userform

Quote from Luke M;731108

How about this then? Hid the rows on Sheet 1, protected sheet 1 (no protection currently). Password line added into code for Save click. Form opens on Workbook_Open.
For the blue bar and red X, it's possible, but it's very complicated and buggy to try and hide it. IMO, it's not worth the effort and error proofing. I did add a line to make sure user can't close form via red X. Although, clicking the 'close' button doesn't really do anything special, so I'm not sure why user couldn't use the red X...

Hi Luke M and all,

Sorry for being late......My project is completed and ready thanks to all of your help..... now I have submitted the project and it is accepted by the management.
IOU for this this....

Thanks a lot guys... you people are just awesome.

Re: Add 1 each time a button is clicked on userform

Hi luke that's great this is exactly what I was looking for. I love this forum because it has talented people like you.

Would appreciate a little more help on this...

Is there a way we can hide all the rows and columns in the sheet1 so that only the userform show up for designing purposes only and also advice if we can remove or hide the blue ribbon and the close 'X' from the userform and make the userform form appear on workbook_open() event and gets closed only via clicking the close button only.

I also want to protect the sheet 2 so, that it can't be manipulated by others once the data is captured. I have used:

/code/
protect.worksheet= "password here"

But, its not working...

Hi everyone,
I have a userform to capture data to a hidden sheet say
'sheet 2' in the workbook. I know how to code to capture
data clicking the save button but, I want help with a
dynamic button named "Add" in the userform in the
workbook attached.
There is a textbox in front of the "Add" button what I want
here is whenever someone click the "Add" button 1 gets
added to the value in the textbox adjacent to it and this
textbox should have number entry.
There's also a listbox in the userform also help me get the
listbox working and data getting captured to 'Sheet 2' (sheet
2 shall be hidden).

Thanks in advance to all of you

Re: Live/Running Clock on Excel Sheet

Quote from cytop;729773

Nobody can help based on the information in your message - try the 'Possible Answers' section below.

I am using the below code:

Sub Recalc()
Range("a1").Value = Format(Now, "dd-mmm-yy")
Range("C1").Value = Format(Time, "hh:mm:ss AM/PM")


Call SetTime
End Sub


Sub SetTime()
Autorun
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "Recalc"
End Sub

in thisWorkbook

Private Sub Workbook_Open ()
Call Recalc
End sub

It say's object not defined error 1004

Would be helpful if anyone can find me the error or anything wrong wrong I am doing here.....

Hi

I am trying to add a running watch/clock in one of my project which have multiple userform and Modules and one excel sheet only. I have tried many a times but, everytime it say's object not defined or error code 1004.

As there are multiple forms and modules I am also a bit confused as to where shall I put a particular code. I want to have the clock started on Workbook_open() event and not using a commandbutton.

Thanks
Sumit

Re: To create a Sheet With multiple buttons to open multiple Userforms

hi mikerickson,

You are great, because of you I have started to learn how to use multipage and other........thanks a ton.

i have made a sample for my use please see the attachment......

After you click the button on the sheet a form opens up which have 5 pages and there are 5 buttons each in all the pages (MBR, Dashboard, Daily indicator, training MIS, Links).

What I want to do here is whenever anyone click any of these 5 buttons (MBR, Dashboard, Daily indicator, training MIS or Links) two more buttons or options shall show up namely, "2013" and "2014" and each of these two buttons shall have there respective monthly buttons [12 months (jan to Dec)]or options and when we select any of the months a pre determined folder or excel file should open (like a link).

I have to present this to my management as a reporting tool so that whenever they want to check any report they can simply select type of report than year and than month and they get what they want. I will also appreciate if you can help me to make this sheet look attractive to my managers.

I know this may be easier for you but, for me it's quite a task.

thanks in advance again.

Thanks
Sumit

Re: To create a Sheet With multiple buttons to open multiple Userforms

Thanks for your reply.
You might be correct but, i am quite new to VBA and don't have much knowledge of howto use multipage control. Would be more helpful if you can provide me an sample.[

QUOTE=mikerickson;727836]A button on a sheet that opens a user form is simple enough.
Instead of multiple userforms, you might consider a userform with a multipage control.[/QUOTE]

I am trying to find a way to have a command button open a userform having 2 buttons on it say "2013" and "2014" and when the user click one of them it opens another form having few more buttons on it say "Dashboard", Dashboard1". Dashboard2", dashboard3" and etc.
What I am looking for is like 5 command buttons that will be placed on an excel sheet. The user will press a button and it will open a form with 2 buttons say "2013" and "2014".and when the user click one of them it opens another form having few more buttons on it say "Dashboard", Dashboard1". Dashboard2", dashboard3" and etc. Clicking any of these buttons with links should open a particular folder. The part I really know nothing about is how can I insert a userform inside a userform and so on.

the form should be something like this on a spreadsheet:
Button1 button2 Button3 button4 Button5
I
Button1 (2013)
I
Jan
Feb
Mar
Apr
May ------- Possibly in a listbox
Jun
Jul
Aug
Sep
Oct
Nov
Dec
I
When anyone of the above months is selected a userform having few more buttons say "Dashboard", Dashboard1". Dashboard2", dashboard3" should open and clicking any of these buttons should take us to some pre defined folders.

I am working on a sample right now will attach it for your reference but, if any one of you have any alternate ideas to do this please advice.

Thanks in advance

Sumit

Hi,

Can anyone help me on this simple vlookup problem. I know this may be a easy one but not for me.
Please have a look at the attachment....
What I want to do is to apply vlookup in cell E2 and whenever we apply vlookup taking lookup value 'A2' the value in the adjacent cells B2, C2 and D2 should show up in the "Ans' column cell "E2".

Thanks In advance
:thumbcoo:
Sam

Re: To Automate Excel Workbook having Text Data

Hi Smallman,

I want to bother you again....

In sheet1 dropdown box we can select any MT number and click view plan to show details. I found out that we can check all the MTXXX's plan in the drop down one after other and details of all the plans can be viewed together as we keep selecting from the drop down box.
What I want is that we should View only one plan at a time by clicking the "View Plan" button and until and unless we click the clear button we should not be able to check another one from the dropdown.

One more thing I tried to hide the "All Plan 2012-13" sheet but, when I hid the sheet the "Sheet1" doesn't works. I want to keep the "All Plan 2012-13" sheet hidden from other please advice on this also.

I am very thankful to you for whatever help you have provided me.

Thanks

Sam

Re: Create a Production Time Recording Excel Sheet

I'll try doing it myself..... Thanks a lo......t

Re: Create a Production Time Recording Excel Sheet

royUK

Thank you for reminding me of the error in my writing.....

Now,

Would you please help me out on this????????

Hi All,

I need your support in creating a Time recording sheet for my colleagues in excel and possibly some VBA.
I don't have a sample to it as this would be a first time for me.

There should be a Login and Logout Button and a Start time end time button to record data to the next sheet in the workbook. Also it would be great if it includes a Break and EndBreak button so that the time taken in the breaks gets recorded in the next sheet in the Workbook.

Every Morning colleagues should click the LoginButton to start and click the logout button at the Day end to get the total production time recorded in the next sheet. (Total production time should be Total time taken minus breaks). We have a 9 hour shift.

Please see if anybody can help me out on this?
Thanks In Advance
Sam

Re: To Automate Excel Workbook having Text Data

Hi Smallman,

You have been very helpful...
I will ask for one more help from you?

Can we hide the 2nd worksheet "All Plan 2012-13" in the work book and protect it with a password?

One more thimg there will be only one plan for every plan code i,e. MT285, MT211, MT147, etc will appear only once.... But, whenever i select MT285 it shows multiple rows of MT285 and when I delete the multiple MT285 from the 2nd Sheet "All Plan 2012-13" the empty bordered rows shows up with the MT285. I will request to see if we can remove this multiple bordered rows whenver I select a Plan only the details of that particular plan in single row only should show up and not including any other blank bordered rows.

Thanks in Advance

Sam