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.
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?
View 4 Replies
ADVERTISEMENT
Apr 14, 2009
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?
View 14 Replies
View Related
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
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
View 6 Replies
View Related
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
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
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
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:
=IF(AND(F8)=0,""*(IF(AND(F8)
View 9 Replies
View Related
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
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
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
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?
Capture.JPG
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
Dec 6, 2013
[URL]
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. !
VB:
Option Explicit
Dim ws As Worksheet
Dim Userpasswords As Worksheet
[Code]....
View 5 Replies
View Related
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 user’s 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 don’t care if ALL boxes are always visible, as long as they are activated or disabled (grayed out) based on user’s 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
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
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.
VB:
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
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
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
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
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
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