SOLVED
Home

Macro enabled spreadsheet problems - Date picker (calendar)

%3CLINGO-SUB%20id%3D%22lingo-sub-279399%22%20slang%3D%22en-US%22%3EMacro%20enabled%20spreadsheet%20problems%20-%20Date%20picker%20(calendar)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279399%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3EI%20created%20a%20macro%20for%20a%20date%20picker%20calendar%20type%20thing%20and%20saved%20the%20file%20as%20a%20macro%20enabled%20S%2Fs%20with%20the%26nbsp%3B%20.xlsm%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20this%20spreadsheet%20have%20to%20be%20open%20each%20time%20I%20want%20to%20use%20the%20date%20picker%20to%20input%20dates%20in%20cells%20in%20any%20Excel%20spreadsheet%3F%20We%20are%20a%20team%20of%20seven%20and%20work%20on%20shared%20spreadsheets.%20Will%20this%20affect%20the%20macro%20and%20will%20they%20also%20need%20to%20have%20the%20macro%20spreadsheet%20open%20individually%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-279399%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-280651%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20enabled%20spreadsheet%20problems%20-%20Date%20picker%20(calendar)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280651%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Joy%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20note%20that%20the%20solution%20in%20the%20links%20is%20based%20on%20the%20Get%20%26amp%3B%20Transform%20(aka%20Power%20Query)%20not%20in%20a%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Power%20Query%20is%20the%20best%20solution%20when%20you%20want%20to%20combine%2Fappend%20multiple%20tables%20have%20the%20same%20fields%20in%20one%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20spreadsheets%20are%20saved%20in%20a%20network%20drive%2C%20you%20need%20to%20move%20them%20into%20one%20folder%20to%20be%20able%20to%20use%20the%20solution%20that%20explained%20in%20the%20second%20link.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20fact%2C%20I%20provided%20you%20with%20these%20links%20because%20it's%20hard%20to%20explain%20this%20process%20in%20a%20reply%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EHaytham%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-280511%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20enabled%20spreadsheet%20problems%20-%20Date%20picker%20(calendar)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280511%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Haytham%2C%3C%2FP%3E%3CP%3Equestion%20on%20this%20again.%20If%20the%20spreadsheets%20we%20use%20are%20saved%20on%20a%20network%2C%20how%20will%20this%20macro%20work%3F%20Will%20I%20have%20to%20save%20it%20to%20the%20laptop%20of%20each%20team%20member%20so%20when%20they%20open%20these%20network%20files%20it%20will%20run%20or%20is%20there%20a%20way%20of%20saving%20the%20macro%20on%20the%20network%20location%20itself%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-280361%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20enabled%20spreadsheet%20problems%20-%20Date%20picker%20(calendar)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280361%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Joy%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20don't%20need%20to%20a%20macro%20to%20do%20that%20as%20it's%20can%20be%20done%20by%20using%20Power%20Query.%3C%2FP%3E%3CP%3EPlease%20follow%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.mrexcel.com%2Fexcel-tips%2Fcombine-4-sheets%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Elink%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20spreadsheets%20are%20in%20different%20workbooks%2C%20please%20check%20out%20this%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DldoQws7Zbx8%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Elink%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-280116%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20enabled%20spreadsheet%20problems%20-%20Date%20picker%20(calendar)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280116%22%20slang%3D%22en-US%22%3E%3CP%3EOne%20other%20thing%20Haytham.%20Can%20you%20send%20me%20some%20links%20on%20how%20to%20transfer%20data%20on%20an%20Excel%20form%20into%20a%20spreadsheet%20or%20on%20a%20word%20document%20to%20a%20spreadsheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20form%20that%20users%20complete%20with%20certain%20fields%20having%20the%20same%20name%20as%20column%20headings%20in%20my%20spreadsheet.%20I%20want%20to%20be%20able%20to%20transfer%20all%20that%20captured%20data%20in%20the%20Excel%20form%20into%20a%20spreadsheet%20preferably%20at%20the%20click%20of%20a%20button.%20I%20am%20guessing%20a%20macro%20will%20be%20ideal%20or%20is%20this%20more%20of%20a%20pivot%20table%20table.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-280004%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20enabled%20spreadsheet%20problems%20-%20Date%20picker%20(calendar)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280004%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Tanya.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279610%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20enabled%20spreadsheet%20problems%20-%20Date%20picker%20(calendar)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279610%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Haytham!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279509%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20enabled%20spreadsheet%20problems%20-%20Date%20picker%20(calendar)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279509%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Joy%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20save%20the%20macro%20in%20the%20VBA%20project%20of%20a%20specific%20workbook%2C%20then%20you%20have%20to%20open%20this%20workbook%20to%20access%20the%20macro.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20you%20can%20save%20the%20macro%20in%20the%20Personal%20Macro%20Workbook%2C%20in%20order%20to%20make%20it%20available%20to%20all%20open%20workbooks.%20Please%20check%20out%20this%20%3CA%20href%3D%22https%3A%2F%2Ftrumpexcel.com%2Fpersonal-macro-workbook%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Elink%3C%2FA%3E.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20regards%20to%20sharing%20the%20workbook%20with%20other%20people%2C%20please%20note%20that%20the%20using%20macro%20is%20one%20of%20the%20limitations%20of%20the%20shared%20workbook%2C%20please%20check%20out%20this%20other%20%3CA%20href%3D%22https%3A%2F%2Fwww.ablebits.com%2Foffice-addins-blog%2F2017%2F08%2F02%2Fexcel-shared-workbook-share-file-multiple-users%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Elink%3C%2FA%3E%20for%20more%20info.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-279411%22%20slang%3D%22en-US%22%3ERe%3A%20Macro%20enabled%20spreadsheet%20problems%20-%20Date%20picker%20(calendar)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-279411%22%20slang%3D%22en-US%22%3EWe%20have%20the%20same%20issue%2C%20and%20all%20the%20reading%20I%20have%20done%20leads%20to%20'No'!%20-%20see%20this%20short%20support%20article%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fwork-with-macros-in-excel-online-98784ad0-898c-43aa-a1da-4f0fb5014343%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fwork-with-macros-in-excel-online-98784ad0-898c-43aa-a1da-4f0fb5014343%3C%2FA%3E%20There%20are%20loads%20of%20similar%20requests%20for%20this%20in%20the%20Excel%20UserVoice.%3C%2FLINGO-BODY%3E
Joy Amobi
Occasional Contributor

Hi all,

I created a macro for a date picker calendar type thing and saved the file as a macro enabled S/s with the  .xlsm format.

 

Does this spreadsheet have to be open each time I want to use the date picker to input dates in cells in any Excel spreadsheet? We are a team of seven and work on shared spreadsheets. Will this affect the macro and will they also need to have the macro spreadsheet open individually?

 

Thanks

 

 

8 Replies
We have the same issue, and all the reading I have done leads to 'No'! - see this short support article https://support.office.com/en-us/article/work-with-macros-in-excel-online-98784ad0-898c-43aa-a1da-4f... There are loads of similar requests for this in the Excel UserVoice.
Solution

Hi Joy,

 

If you save the macro in the VBA project of a specific workbook, then you have to open this workbook to access the macro.

 

But you can save the macro in the Personal Macro Workbook, in order to make it available to all open workbooks. Please check out this link.

 

With regards to sharing the workbook with other people, please note that the using macro is one of the limitations of the shared workbook, please check out this other link for more info.

 

Hope that helps

Highlighted

One other thing Haytham. Can you send me some links on how to transfer data on an Excel form into a spreadsheet or on a word document to a spreadsheet.

 

I have a form that users complete with certain fields having the same name as column headings in my spreadsheet. I want to be able to transfer all that captured data in the Excel form into a spreadsheet preferably at the click of a button. I am guessing a macro will be ideal or is this more of a pivot table table. 

Hi Joy,

 

I think you don't need to a macro to do that as it's can be done by using Power Query.

Please follow this link.

 

If the spreadsheets are in different workbooks, please check out this link.

Hi Haytham,

question on this again. If the spreadsheets we use are saved on a network, how will this macro work? Will I have to save it to the laptop of each team member so when they open these network files it will run or is there a way of saving the macro on the network location itself?

Hi Joy,

 

Please note that the solution in the links is based on the Get & Transform (aka Power Query) not in a macro.

 

The Power Query is the best solution when you want to combine/append multiple tables have the same fields in one table.

 

If the spreadsheets are saved in a network drive, you need to move them into one folder to be able to use the solution that explained in the second link.

 

In fact, I provided you with these links because it's hard to explain this process in a reply here.

 

Regards,

Haytham