Is there a shortcut in doing this instead of copy pasting it?
I have a record just like this with about 572+ rows and 13 repeating items with that 13 items having their own column so copy pasting is really a tedious task. Kindly please help me solve this?
I'm using the following code to create a customized userform, and it works perfectly on my computer, as I've enabled all macro settings.
Code: Function GetOption(OpArray, Default, Title) Dim TempForm 'As VBComponent Dim NewCheckBox As MSForms.CheckBox Dim NewLabel As MSForms.Label Dim NewCommandButton1 As MSForms.CommandButton
[Code] .......
However when my coworkers need to use the code it creates an error, as the VBA project is unsafe.
Now my question is, can you write some code that enables all macro settings temporarily, in such a way that my coworkers can use this code?
My company uses a customized accumulative code week system, as in the year 2000 to today is about 442 weeks with the year 2008 starting around week 416.
Here is how my formula works(or how I would like it to work), if the date (in cell F8) is blank, nothing is displayed, if there is a date (format 2008-07-03) and it falls within 2008, WEEKNUM+416(416 is a fixed reference in J4), if the date falls within 2009, WEEKNUM+416(J4)+52, if the date falls within 2010, WEEKNUM+416(J4)+104. I only need it to go from 2008 to 2010.
This way J4 is a fixed code week reference where 416 is added to each week number, but it does not work for years 2009 and 2010 as it does not add the weeks from the starting reference.
I need to create a grouped sequence that changes every 10 rows.
So in Column A cell A2 the number would start at 01349 and every 10 cells it would decrease by 1 and go on down to 0 so it would look something like this:
I am trying to create a number counter which runs up to a value which is determined by a COUNTIF Statement in the spreadsheet. I can get the number counter to run to the given value. However, due do formatting reasons I do not want the counter to start in cell A1 but A3 instead.
Essentially my code looks like this: ... ActiveSheet.Cells(3, 1).Select Dim i As Integer For i = 1 To Range("A1") Cells(i, 1) = i Next i ...
I'm trying to write some VBA code to do the following (don't think its possible using formulas only). In column A i have a list of codes (A1:A32) in column B i have a value (between 1 and 32 in this case, but feasibly could be any integer) I am trying to achive an output in column C which would take the code from col A and then "count up to" the value in col B;- for example "AAA" in col A, and "4" in column B would return AAA1, AAA2, AAA3, AAA4 in column C. For the next code say BBB and corresponding value, would start its sequence in the next empty cell below AAA4 in col C.
I am creating an excel worksheet. Every time a user opens the worksheet (from a template), I want to get the next numeric sequence number as a reference, showing it in the sheet. The template is available to multiple users but the sequence number should be unique, ie if user Tom has sequence 1, Jerry will get 2, then Anne will get 3 and Tom's next one will be 4. I considered an ASCII text file with a number in, eg 1. Upon opening get OPEN text file, READ text file, Convert to numeric.. add 1.. use this. Convert to string.. WRITE text file. It seems clumsy and since I can only open with READ or WRITE (append no good, since I want only 1 value held), the READ allows multiple users to access simulataneously, and potentially get the same sequence.
Is it possinle to: LOCK file OPEN file for read READ file CLOSE file OPEN file for write WRITE file CLOSE file UNCLOCK file
There are likely to be several users accessing the template and the chance of them opening it at the same time is limited, but possible.
I'm trying to work with a user form with a customized SUM button.
What I want is that the user can select a range and then press SUM and it will sum (and store) the range and then the user can click another button to place the sum in any other cell.
Here is the code I have on the SUM button but it fails
Code:
Private Sub CommandButton1_Click() MsgBox (Application.Sum(ActiveWorkbook.ActiveSheet.Selected)) End Sub
I'm using a userform1.show vbmodeless so the user can click off the form. I tested it being able to work cross workbooks and sheets and I could see where it was returning the activeworkbook/worksheet but I don't understand the range/selected part.
I have a very big Excel file (62 MB). I need to be able to filter by one column (FACILITY) and sort the whole file using the (REGISTRATION NO) column as well as finding the missing sequence number in the (REGISTRATION NO) column. The problem is that the (FACILITY) column has more than 200 different facility name. I am thinking of macro as I have different Spreadsheet I need to do the same steps in each one separately.
I have attached a copy (example from the data that I have) .
Is there a way to have customized cell backgrounds that are more complex that Excel's preset colors and patterns?. For instance, I have a merged cell (it has to stay merged) with a value that is located at the bottom-center of the merged cell. I want to have either a fading gradient (the top of the cell is white fading to a blue bottom towards the value), or just have the bottom portion of the merged cell colored. I have Excel 2003 so I don't know if any other version has this feature.
I need to have all cells locked and protected, but I need to be able to select all cells in column A only. With 2003 I can chose to be able to select locked cells in the Protection Properties, but that allows at the same time to select all cells and I want a property to allow selection in column A only.
I have an txtBox in a userForm. I need the value of the userform in a customized format i.e. the user will be able to enter only 5 digit numbers and after the value has been entered the txtBox should dispaly the value with prefix "A".
For example: 1. If user enters values as 56, then txtBox should display as A00056. 2. If user enters values as 12346, then txtBox should display as A12346.
I have a piece of code from a form command button. its supposed to colect data and put it to appropriate cells.
VB: FoundColumn = 0
For Each c In Sheet19.Range("A5:A33").Cells If c.Value = student1.Value Then 'check if date allready exists For Each cc In Sheet19.Range("c4:nc4").Cells If Calendar1.Value = cc.Value Then
[Code] .....
I need this for Sheet19 and to 10 more worksheets like Sheet20, Sheet21 etc. I can manually copy paste the code and change the Sheet19 to whatever but it just does not seem the right thing to do. I tried:
VB: For i = 1 To 6 naming = "Sheet" & i MsgBox naming.Cells(1, 2).Value Next i
I'm making a task sheet for my employees and would like them to see the option in the ribbon automatically that allows you to add a picture. I have made my custom ribbon with the "picture" command right under the Home tab. I want to email my workbook as an attachment to my employees and have that picture button right there in their ribbon when they open up the attachment. Is this possible?
I have created a custom submenu in Excel that uses the "SheetBeforeRightClick" event in the "ThisWorkbook" object to initialize. In the custom submenu, I want to use one of the choices to load a form that I have created. I am trying to do this directly when selecting the submenu entry, by using "MyForm.Show", but this doesn't work. As a workaround I use an intermediary subroutine, "Sub Load_MyForm" where I put the same command, and then it works! Anyone who knows how to load the form directly from the submenu click event without going through an intermediary subroutine?
I have been using a scroll bar in my project to select a value from 0 - 3
Is it possible to have a customised slider (whether a form control or something created from scratch) whereby the numbers 0 1 2 3 are displayed in a grey, red, yellow and green box respectively adjacent to eachother and a slider can be moved over the top to select a value?
I would like to automate with code the process of extracting one worksheet from that workbook, and saving it as a separate workbook, with a file name equal to the text value of a cell (date formatted dd-mm-yy) from within that workbook. (ie d1="10/07/08" save file with one worksheet 10-07-08.xls)
Function MyFunction1(r, n, xrange, yrange) If r <= n Then For i = 1 To 5 MyFunction1 = MyFunction1 + xrange(i) Next Else For i = 1 To 5 MyFunction1 = MyFunction1 + xrange(i) * yrange(i) Next End If End Function
This is just a very basic idea of what I need to do, so it might not make sense why I am doing it, but I am just trying to test that the function works. Anyhow, the thing is that it works with something like this when I input it in a cell in a worksheet: =MyFunction1(RAND(),0.5,{5,6,7,8,9},{10,20,40,50,100})
However, if I try to define the range or set {5,6,7,8,9} with other cells, it does not work. so for example if I try the following:............
so i'm building an application that'll allow users to manipulate records in Excel with just a GUI, using Userforms, Modules and Class Modules. it's all working but i'm feeling like i skipped a little on structuring it properly.
for example (from my Java work in college), you'd call just one object from the main method, which would create a GUI object, and create/manipulate instances of the different Classes when buttons are pushed. basically you one object whose main created other objects, who ran procedures, etc. what i'm hoping for is to make it as modular and easy to maintain as possible. would anyone have a good resource for optimising a medium-sized application? (the tips Excel/VBA Golden Rules. These Should NOT Be Optional were very good, by the way.)
1.On Man' work sheet I Have a Project No (actual one is a big one) and project code,i have made the project code because it cannot be entered on the time sheet as the original project no is very big and i have legends,then Empl nos and OT Rates
Now on the time sheet if i enter the Project Code A to C it should be counted as "P" present for work, rest as usual.
2.On Summary' work sheet Columns D to J should calculate it automatically based on employee no or name and Project No.
I have created a spreadsheet that requires the buttons on a customised tab/ribbon in order to use it.
If this is on my machine where I have customised the ribbon it works fine. However I need to email this spreadsheet to another person - but wanted them also to have my customised ribbon. Is there anyway to do this - or would I have to go and customise their ribbon on their machine?
I have a sheet where some data is entered in A1 to C1 and i use concatenate to string them together. The problem is in cell C1, where I used cell format to customize the number of zeros in front of the number.
EG: A1: Photos B1: 2013 C1: 00001
the concatenate result is Photos2013\1, instead of the desired result of Photos2013\0001.
I have a column of figures in a file sent to me recently which are dates but for some reason have been reversed eg 20140321. This is 21st march 2014. However I have tried formatting the number as a date (as we would normally see it in UK eg 21/03/14 or similar) and I cannot get Excel to recognise it as a date - I just get a long row of asterisks. How do I get a recognisable date sequence?
Just striving to have this kind of "picture arrangement's macro" to work, macro that whenever you choose a certain cell and operate it on hotkey it'll:
1. Create a new folder named "PicturesAuto" beside the excel file (wherever the excel file located).
2. Inside folder "picturesAuto it'll create a new subfolder named by the same name of the sheet related to that cell you operated the macro upon. (let's say this cell placed on sheet named: "happysheet"- that'll be the name of the subfolder created.)
3. Inside the subfolder "happysheet" it'll create a new subfolder named by the text of the cell you activated the macro upon (let's say you initially operated the macro on B5 cell -which has the text "montana" so it'll create "montana" subfolder also inside subfolder "happysheet")
4. Lastly instead of having only the text "montana" in "B5 cell" like we had initially before activating the macro, the macro'll also add to that text "montana" the link to "montana" folder (path of excel file->picturesAuto->happysheet->montana)
The way I'm planning to use this is to add a lot of HR pictures inside those folders created by the macro that related to that specific sheet, and that specific cell I've activated the macro upon. Plus just having those pictures so organized is just outstanding in every scale, and the excel file will keep running smoothly without any resource consuming overload.
Adding a search engine like feature to a spreadsheet we have which has multifarious columns of data on it, here is a screenshot of what it looks like:
[URL]
We have multiple sheets for each tutor like that picture which lists all information of their learners for reference, now I want to do a search feature on the main home page or perhaps on a dedicated "Search" page which allows us to filter out specific information out of those columns, mainly the ability to search by "Tutor" (aka sheet name), "Postcode", "Venue", "End Date", "Learner Name" and "EBS" number...
I've got quite of few excel charts to make and have been looking for a way to automate the process. The chart types include line, pie, and bar. Basically, I have data for each state in the US that I am comparing with national level data. Thus each chart will have national level data and the data for each state. What I need is a macro that I can easily change the parameters for because different worksheets have slightly different layouts. I started by using the macro recorder to make a chart. Then I attempted to edit it so that it would run loops to make additional charts. Below is some sample data for the first worksheet I have and my attempted macro.
Sample Data:
Code: State Characteristic Value07 Value08 Value09 Alaska A 11 23 45 Alaska B 13 22 98 Alaska C 99 91 21 Alabama A 23 14 11 Alabama B 44 62 76 Alabama C 75 47 21[code]....
