Changing Excel Settings Using VBA - Creating Customized Userform

Aug 16, 2013

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.

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?

View 4 Replies


Creating Customized Sequence In Excel

Apr 14, 2009

I have a table


As you can see, a,b and c is repeated times.

How do you make it so that it would look like




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?

View 14 Replies View Related

Excel 2007 :: Userform With Customized SUM Button

Jan 10, 2012

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


Private Sub CommandButton1_Click()
MsgBox (Application.Sum(ActiveWorkbook.ActiveSheet.Selected))
End Sub

I'm using a 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

View 6 Replies View Related

Excel 2010 :: Changing Default Text Box Settings?

Sep 5, 2012

When I place a text box in Excel 2010, the default is for everything to be locked (Box is locked AND text is locked). I want to change the default setting so that when I start Excel fresh, anytime I place a text box the "LOCK TEXT" box is UNCHECKED.

There's a place where you can select "Set as default text box") after you make the changes, but it's not sticking (even in the same document without closing. IE, I make the changes to a text box, then click the option that says "Set as default text box", place a NEW text box immediately in the same document but the text is still locked.

View 3 Replies View Related

Excel 2010 :: Changing Default Blank Page Settings?

Apr 23, 2012

My office recently upgraded to Office 2010 and we would like (in the accounting department that I work in) to change the default number formating in a blank sheet to Number, 0 Decimals, using seperators, from the current default of general format number.

I have looked for the Book.xltx file to replace but can't see it any where.

View 5 Replies View Related

Excel 2007 :: (Save Files In Format) Settings Keeps Changing

Sep 28, 2011

I am having trouble getting Excel 2007 on my work computer to save as .xlsx by default.

I have opened the Office Button > Excel Options > Save > Save files in this format and selected "Excel Workbook (*.xlsx)," and if I save as before closing Excel then it works perfect and saves the file as .xlsx. However if I close Excel, re-open it later and save a new file the default "Save as type:" is "Excel 97-2003 Workbook (*.xls)" and if I open the save options again the "Save files in this format" option is reverted back to "Excel 97-2003 Workbook (*.xls)."

I have finally overcome the [Compatibility Mode] issue by saving a file named "Book.xltx" (not "Book1.xltx") in the two default locations "C:Program FilesMicrosoft OfficeOffice12XLSTART" and "D:Documents and SettingsusernameApplication DataMicrosoftExcelXLSTART" (we use the D: drive at work for personal files). I thought this would solve the save as issue but it hasn't. I have also changed the "Save files in this format" before saving the .xltx files in the locations to apply the settings to those specific files but that hasn't worked.

It is on my work computer so I am limited in what settings I can change because they have them pretty well locked down.

View 7 Replies View Related

Creating A Customized Accumulative Code Week (modified WEEKNUM) Formula

Jul 4, 2008

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:


View 9 Replies View Related

Changing Print Settings Within Single Worksheet

Mar 7, 2014

How do I change the print settings of individual pages within a worksheet. Specifically, I'd like my worksheet to print out 4 pages, the first as portrait and the remaining as landscape.

View 2 Replies View Related

Changing Date And Time Default Settings

Aug 3, 2004

I am creating a spreadsheet on a British-based system using the d/m/y date system as default, but I am unfortunately entering data using the american m/d/y system and would like to change the default to the american system.

View 5 Replies View Related

Excel 2007 :: How To Adjust Protected View Settings But Protected View Tab Is Missing From Trust Centre Settings

Dec 12, 2013

Excel 2007 - need to adjust protected view settings but the protected view tab is missing from my Trust Centre settings - have the following tabs - trusted publisher / location, add-ins, activeX, macro, message bar, external content and privacy options. Document is a revenue authority download and without being able to adjust the protected view settings, can't input data.

View 3 Replies View Related

Suppress Pivot Table Refresh When Changing Custom Name In Field Value Settings?

Apr 30, 2014

I often use the same file/pivot table for both month and weekly reporting. In my source data my field headers show 1,2,3,4..etc. This way I can use for month or weekly. But I often change the "Custom" name on the Field Value Settings to something more descriptive like "Jan" or "Week1"

The problem is that each time I update the the "Custom" name the Pivot Table auto refreshes and it takes forever to re-label all the columns.

Is there a way to suppress this refresh when updating the Custom Field Name?


View 2 Replies View Related

Excel 2003 :: Creating Reference From 1 Userform To Another

Nov 21, 2011

How do you create a reference number to be posted in a userform2 that is opened from an earlier userform1 where you have entered some initialisation data and generated a source reference number. ie set of data has reference X and there will be y items in that set want references in form X01 to Xy (y unlikely to exceed 20). Want to put references 'X01' into the userform2 and cycle through data entry in userform2 from source reference X01 to Xy. (I'm having problems with the transfer from 1 to 2!)

View 2 Replies View Related

Works With US English Settings, Errors If Italian Settings

May 19, 2008

The following line of VBA code executes properly if machine settings are US English, but throws a "Run-time error '1004': Application-defined or object-defined error" ? ...

View 9 Replies View Related

Saving Default Settings In Excel

Aug 20, 2013

I am working on lots of excels, with data queries from sql and pivot tables. Is there a way to set defaults to some settings in excel (not in this specific file).

To be more specific:

1. Import data from SQL: Refresh on opening, save password
2. Formatting of number to have two digits and a comma separator
3. Pivot table: when adding to values sum instead of count, refresh when opening the file

View 1 Replies View Related

Excel 2007 :: Change Colour Settings?

Oct 9, 2011

Is there any way to change the settings in excel 2007, to give me the original color palate from 2003? I want to use the old colors because I don't like the new ones.

View 2 Replies View Related

Excel 2007 :: Custom View - Some Settings Could Not Be Applied

Nov 14, 2012

I have a large spreadsheet from which I need to create separate Reports by hiding various rows and columns. I have followed instructions for creating a couple of Custom Views (View tab, Custom Views, Add, Inserted Name, OK, Saved the document) but nothing happens apart from getting the message "Some view settings could not be applied". In fact none of the view settings were applied.

View 1 Replies View Related

Excel 2007 :: Default Settings For Drawing Shapes?

Jun 14, 2013

how to change the default settings for shapes in Excel 2007? I select a line tool and it is a blue color and I would like to have its default as black

View 6 Replies View Related

Changing Default Settings Of Autofilter...change From "equals" To "contains"

Jun 13, 2006

When i record a macro with an autofilter, the default setting is always "equals". Is there any VBA code or anything to be able to change so that my macro will search in a "contains" setting.

View 9 Replies View Related

Excel 2010 :: Lock Out Trust Center Settings For Disabling All Macros?

Mar 12, 2014

There are some excel documents in a hidden folder that contain sensitive information. Shortcuts to these documents will be provided to users to open them and add data. These documents contain all kinds of macros as well. Once macro disables the 'Save As' feature so that the workbook can not be saved outside of the hidden folder, preventing to some degree a user from saving as a different name and emailing the document with the sensitive data.

This all works fine with Macros enabled. As soon as macros are disabled the 'Save As' prevention is bypassed and the workbook can be saved anywhere.

Is there a way for an administrator to lock out the Trust Center options for disabling all macros?

View 9 Replies View Related

Creating Dropdown Menus With Extensive Changing Content

Jun 11, 2013

I have a problem (I have intermediate knowledge of Excel): lets say that I have two model years for Cadillac: 2013 and 2014, and have 3 car models for year 2013 (ATS, XTS, and CRX) and two car models for year 2014 (ELR and CTS Sedan). I want to create a dropdown menu under "Model Year" that will show me 2013 and 2014. When I click on either one, a dropdown list for all car models for that year can appear in the next column under "Models". Then, for each car model, there are different characteristics that are in other columns, such as horsepower, mileage, etc...I want all of the content to stay on one line (row), but change when I click on a different model.

View 2 Replies View Related

Creating A Userform With VBA

Feb 5, 2009

On a worksheet i have a list of names in Column A the list length can vary this is why i want to create a userform using VBA. The form just wants a checkbox with name next to it for each name in column A. If the checkbox is ticked I want a 'Y' in Column B next to the corresponding name.

View 4 Replies View Related

Creating Login Via A Userform?

Dec 6, 2013


Basically I’m trying to create a login userform that launches once the database opens.

Userform = LoginTextbox = UserformTextbox = PasswordCommand Button = UserloginCommand Button = UsercloseSheet containing passwords = Userpasswords
Usernames are stored vertically in the A column, and Passwords vertically on the B column both starting from row 1.

I’ve been using code found at [URL] but I’m struggling to make it work. Some of the variables listed below may be useless but I’m not entirely sure so I’m sceptical about removing them. !

Option Explicit
Dim ws As Worksheet
Dim Userpasswords As Worksheet


View 5 Replies View Related

Creating An Interactive Userform

Jul 8, 2009

I am a novice VBA user, with a more-than-basic knowledge.

I have created several userforms in the past, but they were all static, meaning, the user was allowed certain options from a variety of combo boxes, and after filling all the data, the result was migrated into a sheet (database).

Now, I am trying to create an INTERACTIVE userform in which certain combo boxes will become available based on a users selection in a previous combo in the same form.

For example:
If - in a Payment combo - the user will select Check, 3 new text boxes will appear (Date, Sum, Bank code) and the user will fill certain data related to his selection.

If, on the other hand, the user will select (in the Payment box) the option Installments, 3 other boxes will appear (Number of installments, first installment month, sum per installment). These boxes can either be text boxes of combo boxes, and here the user will select/fill the relevant data.

Naturally, the previous boxes, related to the Check option, will be made invisible.

Personally, I dont care if ALL boxes are always visible, as long as they are activated or disabled (grayed out) based on users selection in the Payment combo.

The next step will be to migrate the selected/filled data from the form back into a sheet, where the data will be placed in different columns based on the final selection the user made in the form.

View 9 Replies View Related

Creating A Userform With Combobox

Aug 17, 2005

I am a total noob when it comes to userforms. I want to create a userform with a simple combobox that contains 4 different selections. Along with this combobox will be a Submit button and a Cancel button.

View 6 Replies View Related

Changing Dates Using Userform

Nov 19, 2013

I am trying to query my data, which includes a date field. If the user changes the date, I want to perform certain actions. Once I find the record, I can update all the fields with the userform. However, I can't compare the date in the userform with the date I have found so that it will follow a different course of action. Using the following I get the message whether I change the date or not.

If VisitorForm.txtLastVisit.Value <> FoundCell.Offset(0, -3).Value Then
MsgBox "Dates Don't Match", vbExclamation, "Sorry"
Exit Sub
End If

If I change the "<>" to "=", the routine stops whether I change the date or not. Something's working, but not right.

I did find that the userform sets the format to mm/dd/yyyy although the data is mm/dd/yy. But I've tried changing the data format and it made no difference.

View 3 Replies View Related

UserForm Changing Value And Checkboxes?

Apr 2, 2014

So im trying to get this userform to work something like this:

User select "Full" or "Half" from OptionButtons.

If "Full" is checked the value of all TextBoxes should be "2" and if checked "Half" it should be "1".

But then they got an option to remove one or two categories. Lets say the user wants to remove category 1 and 2, the value should be double in category 3 and 4, in this case the value should be "4" in both category 3 and 4.

But if the user only wants to remove one category he/she needs to double one of the other three categories.

So what I am looking for is something like the checkboxes are disable or hidden when it should not be an option for them to be used.

The userform that looks like this:
OptionButton1 "Full"
OptionButton2 "Half"

TextBox1 (Category1)
TextBox2 (Category2)
TextBox3 (Category3)
TextBox4 (Category4)

And 8 CheckBox's

The fil: TestForm.xls

View 3 Replies View Related

Changing Size Of Userform?

May 29, 2014

I am faced with the task of reducing the size of a group of userforms because of screen size limitations.Some of the forms are quite complex and I am wondering if it is possible to select the form and all its contents and reduce it proportionately. It is possible to do this with the form frame but I have not found a way to include the controls.

View 3 Replies View Related

Changing The Colours Of A Userform

Oct 8, 2008

I would like to change the colour of a userform both the background colour and the font colour. However, in the colour property box I am met with &H00C0C0C0& (which is Grey). I can select some other colours but not the one I would like to use.

I have tried inserting RGB(194,214,154) however I get the error 'Invaild proprty value'

View 3 Replies View Related

Creating Comments History Box On UserForm?

Sep 4, 2013

I have created a UserForm with 10 text boxes. 9 are input fields and the 10th is a large comments box. I have designed the form so that entry's can be added, searched for and updated. However, I would like all previous data to be stored as history in the 10th text box.

View 5 Replies View Related

Creating Userform For Data Entry

Dec 11, 2013

I just need to make a user form for entering data in one of the sheets ( Pending Confirmed Orders ), requested user form should be used to enter the orders information in columns ( each column in new entry ) and just the rows ( from 6 - 21 ) with noting that row no 16 is to link external pdf file as you will see.

File : [URL] ........

View 1 Replies View Related

Copyrights 2005-15, All rights reserved