Saving To Root Directory

I have an excel form, I want my users to be able to open the form, fill in appropriate data, then click the save button. When the button is clicked, the data in the form should be saved to a new file and then the original form should be reset (clear all values) for a new set of data entry. When the data is saved, I want the new excel sheet to be saved to a location specified within the macro so the user doesn't have any options and I want the title of the new file to be the value of one of the cells.

I'm new to macros and would appreciate any help this board can offer. Thanks...

Hi all.

I have set up a workbook that is sent out to lots of different users. They each keep and use their own copy.

I have set it up so that everything looks OK and is visible on MY screen, but I'm conscious that some users may have different screen sizes, different toolbars set up, and so on, which might make some parts not immediately visible to them.

I have set up an auto-execute macro which automatically sets the zoom factor to best fit, for several of the worksheets, and this works fine.
Here's the code that does it.
Code:

Sheets("WELCOME").Select
    Range("A1:N18").Select
    ActiveWindow.Zoom = True

By repeating this code for each worksheet, I can make each one be zoomed just right.

However, the file contains 8 sheets that are all identically laid out, except the number of rows is different.
What I want to do is go to the worksheet that has the largest number of rows (it's always the same worksheet, so I know which one it is), set the zoom factor for THAT worksheet (which I can do, and it always has the same number of rows), and then take THAT zoom factor, whatever it is - and it will vary depending on the user - and apply that to the other worksheets that have a similar layout.

I could just go through each worksheet and zoom it automatically, but that would mean that some of the sheets looked very large, others very small, and I'd like them to have a consistent appearance.
I could also specify a range on each worksheet that was similar to the appropriate range on the longest worksheet, and zoom that automatically, but that's not ideal either, because some of the row heights vary from sheet to sheet, and again I'll end up with different font sizes.

Anyone know how to do this ?

Workbook A has a cell that gets data from workbook B as an external link.
It does this using the indirect function because it needs to concatenate the
path and filename from other cells.

Problem is that if workbook B is closed, the cell in workbook A shows #REF!.

So can indirect work using closed external files or must the external files
be opened?

Is there a clever way of making that work?

Thanks!

Dear Sirs,

Am in need for this solution very badly and what could be a better place than excelforum !

I have an MS Excel File (2007 version) sample file attached, which has name, designation, blood group and so on. The last column is for hyperlinking photographs of individuals.

In the same folder where I have saved this excel file, are lying photographs of individuals. While scanning the photographs, I have saved them serially i.e. 1,2,3 and so on.

In the Excel file, in last column, I have given the respective serial numbers. In order to hyperlink one has to select that particular Cell, press Ctrl K and you automatically go to the folder containing individual photographs, you select that photo and OK.

Problem :

I have to do this hyperlinking one by one and if there 1000 photos, lot of time is wasted.

Solution Needed :

Just in case of excel formula, which we copy and paste, Can I get a command by virtue of which the column titled Photo or column next to it gets automatically Hyperlinked to respective photo WHEN I copy and paste such command to all cells in that column.

Thanks a million and warm regards ::: Jack

I know this question has been asked a bajillion times, so I apologize for the redundancy.

I am working with an Excel spreadsheet and saving it as a .csv file in order to upload to an application that parses out the .csv data as transactions. The system requires .csv files, so this is how I need to save my doc (with this extension). I have been successful at preventing Excel from coverting that long number into scientific format. I have saved as a TXT file, pasted the longer number and it displays correctly. That is all good. But I have to save as a .csv. So if I do that, close the Excel window, and then open again (as the .csv file), the numbers are back to being displayed in scientific format. I have tried creating an Excel doc from scratch and entering text in Text format, to see if this created a cleaner file. But again, the second I save as .csv, close the window and then open that file up again, that dang scientific format is back.

Does anyone have any idea of how to work around this? Once I have successfully gotten the numbers to display as the long-chain number, how can I get them to "stick" so that they don't revert back to scientific format when I reopen the file?

Thanks so much for your help!

Can anyone help me with creating a macro that will save the worksheet as a pdf in a specific folder with the value of a cell and the date. This is what I have so far.

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=Range("H10").Value & Format(Date, "mmdd")

This saves it as the correct cell value + the date, but it saves it to my documents. I need it to save to a shared folder on a network drive.

Any help would be greatly appreciated.
THANKS

Hi,
I need the necessity do delete a sequence of sheets in my workbook.
If I use this code:

With ActiveWorkbook
If .Worksheets.Count >= 5 Then
For n = 5 To .Worksheets.Count
Worksheets(n).Delete
Next n
End If
End With

I receive a confirmation message box with this message:

"Data may exist in the sheet(s) selected for deletion. To permanently delete the data, press Delete" [DELETE] [CANCEL]

I wish to delete all sheets without receiving any message.

Is it possible?

Many thanks in advance for your kind support.

Regards,

Giovanni

Hi all, the title pretty much says it all, I have a form where I am trying to create a button that will pop up a window where the user will browse to the desired folder and once that folder is selected, the path will be copied as a text string into the cell adjacent. My command Button cell is in B3 and the path should be shown in C3.

Can anyone help?

Thanks

Hello everyone!!!!

Here is my situation. I have been trying to learn macros but have not made one yet. Need your help. Here at work, I have some incompentant computer workers who claim they KNOW MS sooo well and have certificates etc... *COUGH COUGH* ya ok... LMAO

We have a "shared" work book we all work in. It's nothing fancy, just use it as a PO Book but since it is shared some people continue to not save before they add in more work so that they see a "refreshed" copy of the workbook before they start writing in cells that already contain information.

How to I create a macro button at the top of the page that is literally a SAVE button.. instead of them saving by going to File - Save or cntrl+s ??? I just thought that MAYBE having a big button in their face will remind them.

Secondly as back up I was wondering if you can create a macro to do a refresh of everyones screens automatically - say every 2 or 5 mins??? I don't mean a save... but a refresh so the screen actually updates in front of you. (although I will keep this little piece of magic out of their knowledge so they don't rely on it, just though it would be a secondary back up to helping eliminate these mishaps cause we are loosing alot of information by people saving overtop of other peoples work and not caring.)

Thanks so much!!!!! Muchly appriciated.

Hello,

Is there a way to create a second worksheet that is based on data from an original sheet that will automatically update when you ADD ROWS to the original sheet?

Linking values between sheets is easy... but I need to be able to set a designated area on my A sheet that I can add rows into which will automatically be added to my B sheet.

Does anyone know of a function that I can apply for this?

Is it possible to share a workbook with macros and have the macros work properly. I have one spreadsheet that needs to be shared by many users. We want to all be in the same document at the same time saving changes. The real catch is that the workbook has macros. I used the 'Share Workbook' function under tools. At the end of the setup, it told me that the macros would not work properly. The odd thing is that everytime I try to click on one of the macros I get an error, but yet the macro still performs the function. So essentially the macro works, it is just a pain because you have to click 'end' everytime on the error screen. Any suggestions?

Alanda

I did a bit of browsing on this problem. Found others suffering the same but haven't found any conclusive answer yet.

Every so often when I attempt to save a file, (including save as), Excel won'r let me. By won't let me I mean:

using Save doesn't appear to do anything using Save As doesn't either do anything, the dialog is not displayed and if I am doing via the File menu then the File menu is exited and the previous ribbon tab is displayed (i.ethe one I was on before clicking 'File') if I close the workbook I am prompted to save, close without saving or cancel. Clicking save just invokes the same msgbox again. I can't work out when it goes into this mode. Some days I can work without this problem, other days I encounter this 2 or 3 times.

The only thing I could suspect was I think this started around about the time I installed xlDennis' code library. I have uninstalled the addin and so far so good, but I cannot categorically say that this was the cause.

Anyone have any idea?

Cheers
Jon

Edit: I have read this: http://support.microsoft.com/kb/271513
Doesn't seem to cover the issue I describe

I'm having a problem in a workbook with several ActiveX command buttons. I had been using the form control buttons to run macros, but the boss wanted each button to have it's own, different color. So I removed the form control buttons and created new ActiveX command buttons. I got into the button properties and set the background colors. I added the _Click code to run the macros when the user clicked the buttons.

All of the buttons were working fine. Then I saved and closed the workbook and went to lunch. Now when I open the workbook, the buttons don't work! When I click them nothing happens. They appear frozen. They don't even seem to click. No error message. Nothing.

If I right-click the button in Design Mode and select Properties, I get sheet properties not the button properties. I can't seem to locate the command button properties any longer. I still see the button name "cmdButtonGetInfo" and "=EMBED("Forms.CommandButton.1","") in the name box and formula bar. The odd thing is if I create a new button it works fine until I save and close the file. When I reopen the file none of the buttons work.

It's like the buttons are being disabled when I close or open the file. Any suggestions?

Hello,

I'm pretty new at VBA and was wondering if you could help me out on this:

I have created a VBA userform but will need to have it used by at least 5 users. My question is, can it be done with all users working at the same time and when saving their work all data entered will go to one master excel sheet? if yes, would you be kind enough to share the code?
MS Access is not an option for me so I was wondering if you could help me do this in excel.

thank you so much and would really appreciate to hear from anyone soon.

I have an excel 2007 file sitting in a shared network folder. I only want one user to be able to make changes at a time (any other users would get a read-only). For some reason it currently does not do this, and I have multiple users with the same doc open. I'm concerned that changes will get over-written when 2 people are saving their changes. Can anyone help me with the settings for this.