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.
View 9 RepliesWhile my excel file is protected, I want to have a functionality that allows user to modify on certain areas (of the file), such as below:
1. After the user select number of years at the drop-down list (Main sheet), rows and columns at other related sheets will shift and hide certain values which corresponded from the drop-down list (e.g. if user selects 3 years, then at other sheets, rows from year 1 till 3 will be displayed, and rows for year 4 and year 5 will be hidden).
2. Users can add rows for entering new data at a new line and delete rows for deleting data at the correspond line.
This PC is using the Excel version 2000. At Tools > Protection > Protect Sheet, the available options are 'Content's, 'Objects' and 'Scenarios'. In order to allow users modifying rows/columns (while the file is protected), options such as 'Format Rows' and 'Format Columns' must be checked. But these options are only available in the later versions I believed.
After I have done macro recording for the protection properties at Excel version 2003 (my sister's PC), this is what I got for the .....
I recieve an error :
Run-time error '50289':
Can't perform operation since the project is protected.
When i try and run my code.
The code does add parts to modules and workbook events, but I need the code to be protected.
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.
View 3 Replies View RelatedI 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
but that gives an object required error.
I have a table
item_desc
a
b
c
a
b
c
a
b
c
As you can see, a,b and c is repeated times.
How do you make it so that it would look like
item_desc
a
b
c
item_desc2
a
b
c
item_desc3
a
b
c
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 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?
View 3 Replies View RelatedI 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?
The workaround (which works) in "ThisWorkbook":
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 have a workbook.
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)
I wrote my function as it is shown below:
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'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.
Excel 2007
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'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.
Here is a copy of my formulas:
=IF(AND(F8)=0,""*(IF(AND(F8)
I have a spreadsheet that I have a lot of macros that are attached to a customized toolbar saved in the same spreadsheet. I saved this is a read-only file. When I open as read-only and run my macros (testing), I save it as another file. When I then open the "template" to do the same thing, the toolbar/buttons now reference the file I previously saved as something else. Help please? Is there a macro that would delete all macros before saving the file as something else?
View 9 Replies View RelatedstoredPath = .CustomDocumentProperties("PathCertString").Value
Although the question I'm about to ask is not related to Excel, but related to MS Word, the coding is similar.
The above code I used to set the properties value, but I get an error highlighting 'storedPath'. I speculate MS Word does not recognized this word. Is there another word or code that I can use to set the value in the MS Word document properties?
Within my code I have restricted the toolbar options that a user can access (i.e. for Menu Option 'Edit''Tools'):
Set myCmd = CommandBars("Worksheet menu bar").Controls("Edit")
myCmd.Controls("Delete Sheet").Enabled = False
But if the user wishes to delete the sheet, they can select the specific WorkSheet 'Tab' and Right-Click to Insert/Delete/Rename the sheet etc.
How do 'hide' these options within VBA? Or is there a Menu setting that I can be set to Enabled = False?
message box properties. i m using this
View 3 Replies View RelatedIs it possible to disable The "Look In:" field of the GetOpenFilename dialogue?
What I would like to do is to keep users from selecting folders other than the CurrentDirectory settings and if possible to keep the user from deleting,copying and pasteing to the files in the current dirrectory displayed. The code I have is:
I would like to programatically add information to an Excel file's Properties, the Details tab. I have alot of files in the applicable group. Files are .xls but I'm using Excel 2007.
View 3 Replies View RelatedIs there a way to set the printer's properties using VBA?
Sometimes we set the printer for BEST quality to do photos or brochures. Well if we don't change it back, when we go to print a spreadsheet, it takes forever because it is set on best quality.
So......
I want to be able to set the print properties to normal using VBA.
I have a problem with the PivatTable properties in Excel and VBA. The problem is as follows:
This is a example table:
Sum of store_sales time_id product_id store_id 367 368 369 1 3 6 7 11,4 11 13 14
(the format is not real clear, but I will explain)
This is a part of a PivotTable where:
Sum of Store_sales is located in the datafield(one record; 11,4)
product_id and store_id are Row-Items
Time_id is a column item.
Now, I want the properties of the cell containing 11,4. I've made it so far in VBA that I can ask what his column-items and his row-item are.
Column = Application.Range(chosenCell).PivotCell.ColumnItems.Item(1)
Row = Application.Range(chosenCell).PivotCell.RowItems.Item(1)
Row2 = Application.Range(chosenCell).PivotCell.RowItems.Item(2)
But how do I get VBA to return the valueheaders of those columns and rows? So actually, I want VBA to also return the names: product_id, store_id and time_id. This is because I need those headers to create a query which I send to a Access database.
Is someone able to give me a hint? Is there a method for this in VBA?
where I can find a comprehensive list of '. Cells()' properties that I can Test for/Apply to Excel Cells?
i.e.
Cells(x,y).NumberFormat
I want to set Conditional Formatting using VBA,
to test for:
Data Type (Character, Integer, Date, Decimal, Logical)
Field Length (x(50), 999, 99/99/9999, 999.99, Yes/No)
from an imported file.
I have a workbook with 10 worksheets and I need to know the memory size for each worksheet. I know from File/ Properties that the file is 3.7mb but that is much higher than I would have expected. I can't tell which worksheets are causing it to be so large.
View 5 Replies View RelatedI have attach the lab2.xls files below.
1. Download “ Range Data.xls”.
2. Use the Offset and End properties of Range object to name range from A2 to the end of the column as “NEmployees”, range B1 to the end of the row as “NScores”,and the rest of the range, B2 to F19, as “ScoreData”.
3. Do some formatting using the range names and the With-End With construction: make the font of the NEmployees Range in bold and blue color; change the font of the NScores Range to italic, in red and centralize the text (using the HorizontalAlignment property).