I'm trying to write a macro attached to a button which will add another sheet to the workbook with a specified name. I'm running into problems when I try and reference this new sheet, since when a sheet is added it's sheet number is not necessarily the next available number. Like if I inserted two sheets, Sheet1 and Sheet2 and deleted one the next sheet I inserted would not be Sheet2 but sheet3. This is causing me some problems in trying to make my code error proof. Here is what I have.
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
I have tables on a spreadsheet and userforms created. Is there anyway to prevent users from just typing on the worksheet so they will have to use the userform?
I've only just starting using Excel for anything other than basic calculations and have got a little stuck with a user form. On my attached spreadsheet I am trying to set up a form for staff incident reporting. There is a Contents worksheet and then each incident has it's own detailed worksheet. The user will click on 'create new incident' on the Contents page.
This opens a user form. From this form I want to populate the contents worksheet. I then also want it to populate the relevant incident worksheet. I can populate the contents page but I need help getting the correct Incident worksheet populated at the same time. Hopefully all will become clear running the file.
I have a worksheet that has been designed to allow the user to enter data, in certain cells of the worksheet are formula to do automatic calculations as the user enters data. Here is my dilemia
a) I need the cells with the formula's to be locked so the user cannot delete or alter them. b) In cases there may be a case where rows will have to be deleted or inserted, depending on the data entered. c) The sheet needs to be protected to avoid the user from altering the sheet i.e. design etc.
I have found the problem is that i can lock out all the cells that i do not want the user to alter, and i can protect the worksheet and allow users to have certain edit right. But the problem i am facing is that when you have locked or protected cells on a wprksheet and you protect the sheet, then you can not delete or insert rows.
Is there a way that allows the sheet and formula to be locked, but also allow the user to delete or insert rows if they need.
I am creating a spreadsheet for work - I have a column that is all the formulas projected sales minus actual sales.
I need to lock this column so that no one BUT MYSELF can change the codes. In other words "idiot proof" the worksheet. there are only 18 sections that I want anyone else to be able to type in.
is there a way to do this without adding users? half the ppl have personal emails and half have company emails.
I have created a form in a worksheet which I have added questions too and then locked all cells except the ones where I want the answers in.
I have then added a button to the bottom of the sheet called "Print and Save". This work sheet is called "form"
On a second sheet called "database", I have all the titles of the questions running from a -> k and nother else.
What I want to happen is the user input the information on "form" into the boxes available. Once they have completed the questions, I want them to be able to click on "Print and Save" so that firstly the page "form" prints, and then for all of the answers to be saved in "database" below the titles for each question.
I have had a play, but just don't know where to start. Also, once there is a row of questions below the titles, I don't want it to overwrite information already there, it needs to go onto the next empty row available.
Attached is my excel file.
You will see in the code that I have the code for printing.. I just found this on the net and it seems to work fine for me.
VBA code to get userform input from form to "Data" worksheet. Data starts at columns A3..AG3. I need the code for the "Input" button to put Month, Date, Type, Comments under their corresponding columns and the "Amount" to go under the appropriate column that is selected by the Option Button. I've included a picture with in "red" comments.
I have a macro that I would like to let a user edit the worksheet and make changes at a certain point. I have tried creating a modeless userform, but the userform only pops up and the code continues running without waiting for input (or running any of the code in the userform). Any way I can accomplish this or what I would be doing wrong with the userform?
I have a workbook which contains multiple worksheets of employees' information. I'm hoping to share this workbook out. Each employee only able to view and update their own worksheet and their manager, being able to view/update everything within the workbook.
I am trying to figure out how to delete a row on a worksheet via a VBA User Form. I currently have a User Form with a Combo Box that is populated by cells in a named range ("PickCategory...") on a worksheet... I am populating thsi box using this
Private Sub UserForm_Activate() ComboBox1.List = Worksheets("data").Range("PickCategory...").Value End Sub
First off, I want to include a lable that will display the contents of the cell to the right of the currently selected entry of the combobox on a label (or textbox). So, whatever entry you select in the combo box, the corredponding value (to the right of it on the worksheet) will appear in the text/label box. Is there something similar to the VLOOKUP that will work in VBA?
Also, what I want to be able to do is to allow the user to select one of the entries from the combo box (which is already working) and then be able to delete the row of that entry on the worksheet. So, the user selects an entry from the combo box and then clicks a "Delete" command button to delete the row of that entry on the worksheet...
Is it possible to show a timer on a worksheet as a user inputs to it? I can get the timer to appear, using Application .OnTime Now() + TimeValue ("00:00:01"), "AutoRecalcWorkbook", but it's kind of modal -- that is, the user can't input at the same time it's running.
I am trying to have an operation run when opening the Workbook. The UserForm opens fine, but I get an error when the GoButton_Click sub runs. The error file is not very comprehensive.
The user is promoted to 'Delete' or 'Cancel' before the sheet is deleted. I do want to select 'Delete' for the user, but I'm not sure how to do this.
The error mentioned occurs after the user selects 'Delete'. The error is:
The object envoked has disconnected from its clients.
VB:
Option Explicit Private Sub UserForm_Initialize() Me.Choices.List = Array("Choice 1", "Choice 2") End Sub Private Sub GoButton_Click() If Templates = "Choice 1" Then
I've recorded this code and am looking to include it in a button. I would like for when a user clicks the button assinged macro that the active sheet is cloned and saved as a CSV file. The user should be prompted before saving on where (file location) they'd like to save the file.
Sub CloneWorksheet() ' ' CloneWorksheet Macro ' ' Sheets("SDW&Customer Workshop scheduled").Select Sheets("SDW&Customer Workshop scheduled").Copy ActiveWorkbook.SaveAs Filename:= _ "C:Documents and Settings1167916My DocumentsTestBook1.csv", FileFormat:= _ xlCSV, CreateBackup:=False End Sub
I have a worksheet named "ABC". In row 4 of this worksheet, each cell from G to BG is numbered from 1 onwards and each cell has a unique number. I would like to have a macro ask the user through a pop-up question box which column data should be calculated in. For example, if the user types "1" in the question box, the macro will go to column G which has "1" in cell G4 and will select G5 the cell just below the column the user defined and calculate the formula.
I have a userform, every time the user clicks on the start button it recodes there name, today’s date and their start time when they hit finish it records their end time.
The next time the user hits start it will record the users name, today’s date and start time on the next row (below their last entry)
The problem I have is that if the user clicks on start and then does not click on end button, but instead exits excel, then reopens the user form and hits start. The start will go on the row below, but now when they click on the end button the end time will go into the previous row for the end time, as they did not put it in if you know what I mean.
I want to stop the user from being able to exit excel if they had clicked on start
Private Sub Workbook_BeforeClose(Cancel As Boolean) Cancel = True MsgBox "You have clicked on Start and therefore mus Click the End button before you can exit excel.", vbCritical, "Cannot Close"
End Sub
This disables the X in the top right hand corner, I have aready disabled the one in the user form. However the following code does not let the user exit via File and Exit. Is there a way to put an IF statement into this that say if CmdStart has been clicked then the user can not exit, How every if it has not then they can exit.
I have read threads on this forum as well as on others and this does seam to be and issue.
OR is there a way of Hiding the X button that is in the top right hand corner. However this will still allows the user to hit start button and then exit excel, which will continue with the problem.
Want to go to a particular sheet in the same workbook by selecting the sheet name from a list in sheet 2 and clicking a command button. Sheet 2 has a list of all sheets in the workbook and sheet names are in two cells in column A & B (e.g. tdm-216). User to select two cells which are the desired sheet name (all sheet names are unique) and then click macro to jump to that sheet in the workbook. There will be over 100 sheets in the workbook.
I'm using Excel 2010. I have a workbook with only 1 worksheet in it and it will be sent to several people. I want that excel file to open in Full View when the end user opens it. Is this possible without any VBA codes?
I have an excel file with multiple sheets. I want my HR team to update that file, but one user shouldnt see the entries of other users. I want to make a consolidated file out of three files. This excel template is for salary input.
I have a user form that has a combo box "City" two text boxes one called "Flight" and the other "Date". What I'm trying to do is to prevent the user from saving the data input from the user form if any of those three fields is left blank. The code that I have so far checks all of the required fields, if any are left blank a message notifys which field(s) is left blank and return the focus back to that field. But the rest of code also fires.
What I really need is either to stop the code if any fields are blank and return the focus back to the blank field, the user completes the field(s) and clicks the save again, or better yet, pause the code until all the required fields are completed and then complete the save. (There is actually another 200+ lines of code in this sub, but I deleted it to keep the post a little shorter.)
I am trying to check a user input to see if the user has entered the date correctly. I am using the code below but it keeps giving me a "Type Mismatch" error.
An Excel file named CLEARING is availble in thr central server system. it will be accessed by 5 or 6 staffs from some other systems what i need is when one person accesses/edits this file the others should not even able to open in the read only file ,when the file is saved and closed ,then only the next person should access it
I have a workbook that is used to track overtime hours worked through the year. I've received some help from users on this forum, and others, to create the necessary macros and formulas to allow a supervisor to make use of the form; however, the workbook is also used to track those that wish to volunteer to work overtime each weekend. The sheet has a list of names for the employees on each shift, then a column to allow a "Y" to be placed under the date - designating them as a volunteer for any overtime available. I've found this spreadsheet (attached - from this site) that will allow individual users to access the workbook and have unique passwords for each. I don't completely understand how it works, but I feel it may be a start to getting what I want.
Essentially, once the user selects their name and inputs their password, I'd like the workbook to be opened and then allow them to only manipulate the row associated with their name. That row will change from week to week, so specific numbers will not be able to be used, but their name will be the same as the name used in the logon.
I feel like I'm asking for a lot here, so just some initial guidance on whether this type of thing is possible would be an acceptable answer.
I need to create a user form that will "pop up" and ask the administrator to enter the above details.
A - should be created automatically (ie last claim number + 1) B - user enters manually C - data retrieved from a list D - data retrieved from a list E - date is the date the information is entered
how to get informazion with VBA code about the name of the user who opened already a workbook on the network.
Usually if somebody wants to open a file that is opened by another user, office applications give informations that the file is open and about the name who opened that file.
Is it possible to get this information with VBA code, too?
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
I have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567 row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789 row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765 row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321 row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
I am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
