Is there an easy way to print out the Red Green and Blue settings (i.e. numerical values) for the current pallette in a workbook?
I can set them fine and transfer them between workbooks but that's not quite what I need.
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 RelatedExcel 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 Relatedi made XL App settings in a way suits my needs...........
Is there a code to restore these settings when i reinstall the XL App ?
Are there any settings that can be applied to a message box? ie changing the background colour or header colour etc.
View 2 Replies View RelatedI have created multiple nametags. I want to use a button which when pressed will run a loop to extract each individual element from the nametag and assigns a value to cell based on that. I have coded for the other functionality that happens when the cell value is changed but can't figure out a way to run the loop.
View 2 Replies View RelatedI have a spreadsheet with several tabs. I am wanting to lookup data in tab "A" based on data in a list. For example:
I may have:
Tab A
Tab B
Tab C
Tab D
Tab E
I have created a validation list containing A, B C, D, E I would like to use Hlookup to look up data imported into the table in Date order.
i have workbook in which I have two sheets, one of them cotains colums with information and in the other sheet I have to create a Search box to retrieve all from any Contact number, of course it has to be based on contact numbers culumn.
example:
CONTACT_NUMBEROPPORTUNITY_TYPEQUARTERCON_TYPECONTRACT_TYPE_DESCRIPTION_LINECURR_CODE
379159OUTSOURCE201002C4PSMARTnet Onsite Premium 2x7x4USD
389859OUTSOURCE201002SNTSMARTnet 85xNBDUSD
379159OUTSOURCE201002C4PSMARTnet Osite Premium 4x7x4USD
388859OUTSOURCE201002SNTSMARTnet 85xNBDUSD
388459OUTSOURCE201002SNTSMARTnet 85xNBDUSD
388459OUTSOURCE201002SNTSMARTnet 85xNBDUSD
and in the other sheet I have to create a search box template that based on the contact number it will retrieve all other information.
When I run it it returns in A1, Jeffrey.Brown2. I would like to turn this into Brown, Jeffrey but because another user may have a shorter or longer name the Left & Right function does not work for every scenario?
Sub who()
Range("A1").Value = Environ("username")
End Sub
how do i change the default settings in excel, for example when i open excel it shows numbers on both rows and columns and i want it to show letters on the colunms. i know how to change the r1c1 reference style but how do i get it to stay the way i want?
View 4 Replies View RelatedI have developed a process design application in Excel using User Forms to input data. The data are placed on a sheet of a workbook with the majority of the calculations being done by cell-to-cell calculations so that users can view the formulas if they so desire. This application works well in English. We want to use the same application in our German office where it has not worked well.
In order to trouble shoot the problems, I can go to the Region and Language setting under Control Panel and set it to German (Germany). This converts the number format to a period for the thousands separator and a comma for the decimal. This seems to create a lot of problems for VBA that I have not been able to sort out.
To simplify the problem I have written a very simple program. It has one User Form with two text boxes for number input, an Enter command button and a Close command button. Sheet 1 has a command button that opens the User Form. In the User Form, I enter a number in both text boxes and click Enter. VBA code then enters the number from Text Box 1 in Cell A1 and the number from Text Box 2 in Cell A2. Cell A3 has an equation that calculates the sum of A1 and A2. All cells are formatted as General.
The workbook works as expected in English. When I set my PC to German (Germany) the application works as expected when I enter an integer in the text boxes. However, if I enter a decimal such as, in the German format, 10,5 and 5,5, the numbers entered into the spreadsheet are text (left justified) and the sum is zero. I have to enter 10.5 and 5.5 to get the numbers to enter as actual numbers and give the correct sum.
How to make VBA work with the German settings?
I have a large file of data and the data looks like this repetitively, however, there are also useless data. But I figure out that the repeat data looks the same compare to the useless data. I need to extract the data that I only need. Is there a way to set a macro to search for a String from the beginning of the excel file, when the string is found, it will keep the 2 rows above and 56 row below it, then loop.
Here is an example of it:
A1 USELESS DATA
A2 USELESS DATA
A3 USELESS DATA
A4 USELESS DATA
A5 GOOD DATA
[Code] ....
The macro will start from A1 then go down till find STRING, then will keep 2 row above it which is A5+56 row below it which is A63.
Then continue at A64, then find STRING at A66 and do the same which is copy A64 (2 row above)+till A122 (which is 56 row below), then continue.
Since A123 to A155 does have any STRING, they will all be deleted..
I change the international setting by using the API call shown below. The settings change, but is not activated before I exit Excel and go in again.
View 7 Replies View RelatedEnable Macros to open it. No one else does.
Second, all the rest of us can click the print button to print the one single page that we are viewing. He says when he does this, that each time it automatically starts printing all the pages in the workbook. He wants to only print the one page.
Is there a way to force a print RANGE, and add settings such as margins, and print to range to 1 page wide and 1 page tall? Can this be set with VBA? I tried to make a marcro but it crashes. This is for Excel 2002.
View 9 Replies View RelatedIn microsoft word you can clear find criteria like below.
With Selection.Find
.clearformatting
.Replacement.clearformatting
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindStop
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
At my company we need to print our workbooks containing 3 to 24 worksheets. The first page should be printed on different paper than the other pages. From various forums I gathered that it's not possible to set the papertray using vba. Those forums suggest 3 things: 1. Windows Api functions (don't work at our company(security)), 2. SendKeys (don't work because of different printers, office versions and future-proofness) and 3.
Define printerdrivers per tray.
This last I did, so there are printerdrivers for tray3 and tray4.
With the following code I try to print the workbook:
Public Sub printSheets(strP1 As String, strP2 As String)
Dim curPrinter As String, firstPage As Worksheet, otherSheet As Worksheet, x As Integer
Set firstPage = Application. ActiveWorkbook.Sheets(1)
curPrinter = Application.ActivePrinter
Application.ActivePrinter = strP1
firstPage.PrintOut
The problem: The sheets are sent to the printers specified as strP1 and strP2, but the tray-settings from these printers are "overruled" by Excel; they are printed from the papertray that was the default of the printer when Excel was started.
Also, using the default printer dialog from Excel has the same problem; when the (windows-)default printer is Tray3 then all the pages come from there, even when printer Tray4 is selected as the printer. In Word or Acrobat etc the prints come from the right tray.
retrieving data from financial website databases like yahoofinance.com and bloomberg.com. I'm trying to make an automatic stock analysis model to read from the website database and retrieve the data into excel sheets. For example, when opening the excel model the user gets a popup to enter the stock ticker, the user enters the ticker and gets a set of data. Is this do-able in excel?
View 2 Replies View Relatedi have a long list of what were once file names in excel that i need to retrieve data from. i have attached an example file with 2 file names which i recomend to view while reading this request.the file has 2 spreadsheets. the first one is just the file name in the format in which i recieve it. the second one is a table that i need to fill out from the data in those file names.
i have a problem with the following columns in spreadsheet 2:
1. column C: i have the command to copy the site name as it is to this column, but what i need is for the program to read if the site name is ZANUAH or ADORA and then write only Z or A. note that these two site names have a different number of charecters in them.
2. columnd D: similar problem. i need it to read the lab name and write AL if its MAGAMA, BA if its Ben-Ari and SH if its shafir. i have the command for excel to simply copy the word, but how do i make it write the letters that represent the lab name rather than the lab name itself?
3. column E: the report number is the 6 digit number in the file name. i have the command which retrieves it, but it has trouble when the length of the number changes. its important to note that sometimes the number might contain non-numerical characters like 219641-1.
4. column O:i have the command to get the data from the parentheses next to PSD in the file name into a box. what i need is to get it to copy just the letters C or NC from the file name into this column, without the number.
5. column P: same as column O, but here i need just the number, without the C or NC.
6. column T: all i need is for it to copy the last 2 letters from the file name, which i know how to do. the problem is that since the file names come with a .pdf at the end, all i get is df. so in fact i need it to copy th 6th and the 5th letters from the end of the file name, which is above my abilities.
I am trying to get the data out of a cell and put it in a textbox in my userform.
What I have is a Worksheet that has autofilter on. After the user clicks certain objectbuttons, there is only one row, that has data in it, displayed. The cell I'm after will always be in column A and be the second visible row.
I have a fairly simple matrix table that im needing help in which way to retrieve the information correctly.
Starting in row 10...
My Column A has length that i want to return
My Column B has a minimum value that i need A2 to be larger than, say 20
My Column C has a maximum value that i need A2 to be smaller than.
My Columns C & D, E & F, G & H and so on then have different sizes also available... but thats for me to work out later.
Is the best way to achieve this with a series of If statements as Vlookup only returns values to the right, and i want the value on the left, or what is my best way of tackling this problem.
I can attach a sheet that i started if the above makes no sense but my formula is useless so far...
=IFERROR(SEARCH("[",string,1),MID(string position,start of char,length of char))
Hi I wish to pull out the characters from a sentence. Once it detects the "[" from the sentence it should pull the string that follow limiting to the length of the character.
It looks like I did something wrong and the results shows only the position of "[" only.
I want to error check because it to return nuthing if there's no value. IF statement would process errors in this case.
I have the following in Column A. 01. Artist - Title
I manage to put 01 in Column B and Title in Column D
I am struggling to split between the "." and the "-" to show Artist in Column C
I am using in Column B
I am facing the some issue while tried to display the data from array. Below is code I have to tried to display the data from array.
Scenario: I have assigned the few values in my excel to an array and tried to display the data stored in array
Sub Main()
Dim Rows_Array() As Variant
Dim i As Integer,
Sheets("EmployeeWise").Select
Rows_Array = Range("C6:C" & Range("C65536").End(xlUp).Row).Value
For i = 1 To UBound(Rows_Array())
MsgBox Rows_Array(i)
Next
End Sub
I am looking to find the rider names of the first top 3 position for each stage in Results Page. The data comes from Stage Results. Initially I used Index and Match but to no avail.
Results Page
Stage 1
1
First
2
Second
3
Third
Stage ResultsA
B
C
D
(E)Position
1
Guy LeForge
Commage
6334.030
33
[Code] ..........
How to retrieve value from cells to textboxes, DTPicker, ListBox in a userform ...
View 7 Replies View RelatedI need to retrieve 6 digits (It's really the date in "ddmmyy" format) from a string. If the strings were to follow a standardized format it would be a simple matter of using left, right or mid. However, I have a couple of different scenarios.
The "ddmmyy" I'm looking for may be sandwiched between other characters, separated by a space or remain at the end of the string. Is there a way to sniff out the 6 digits in the string regardless of it's position?
Here are some examples:
1) ABC COMPANY 3.6% 100111-BLAH
2) MCCAIN JOHN4.9%120509
3) ROYAL BK SCOTLAND 4.03% 160309
4) IBRD 8% 090210 BNPHFN00002 HKD5) AUST & NZ BANKING 4.23%161109-EMTN
I'm using this code to input values into the next available row of columns of A and B.
Range("A65536").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Textbox1
Range("B65536").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Textbox2
I have this piece of code right under it:
Range("E65536").End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = Range("B65536").End(xlUp).Offset(1) - Range("A65536").End(xlUp).Offset(1)
The value being returned is 0.
What I want that last part to do is take the last row of column B and subtract the last row in column A.
how to retrive and display Data. I've attached an excel file with the macros. That what I need is to be coded in Module1.
View 6 Replies View RelatedI have tried to find how to retrieve a worksheet from a closed workbook and I am almost sure that there was an answer how to do this in FAQ but I cannot seem to find it, does anyone know where this thread is?
View 7 Replies View Related