Search results

Hi the forum, First of all, I would like to wish everyone a happy 2017 I try to solve the following two problems. I have two files, an Excel one and a Word document. The Word document contents some text referenced by bookmarks. The Excel file has one sheet containing a combobox and a...

Hi the forum, I want to create a chart with dates in horizontal axis and hours in vertical axis. I have two columns showing by date col1) the time of sunrise (col2). The two columns have the same content with a different format. For dates: dd-mm-yyyy h:mm:ss displayed as dd-mm-yyyy For hours...

Hi the forum, I want to submit the forum the following problem: In a VBA procedure, I feed an array variable (Dim Temp_Max () As Variant, ReDim Temp_Max(200)) with temperatures (numeric values): the_temps_Max(i_temp) = Worksheets("DATA").Cells(x, 4).Value Same reasoning with years...

Hi the forum, In a procedure, I compute the monthly average of temperature between the years 1833 and 2015. Everything goes fine until 12/31/1929. When switching to 01/01/1930, 1930 becomes 30 and is considered as being less than 1900. Below you will find in the pictures the values in the...

Hello the forum, I wrote a small application to manage cascading dropdowns. It uses the technique of "scripting dictionary." As you can see in the attached file, in the "cbx_countries_change" procedure, I use a classical comparison to select the "items" because I do not understand the...

Hi the forum, I try to write a VBA application to analyze the results of the participants in several events. As you can see on the picture in attachment, some participants do not complete all events. Based on the pivot table, a pivot chart is build. The name of the participants who do not...

Hi the forum, I’m facing with the following problem. How can I, in VBA, change the category label when, in a pivotchart, I switch the Row/Column? As illustration, in the attached file, select a “team” in the Team worksheet. A pivotchart is shown. In the pivotchart sheet, a click on the...

Hi the forum, Since yesterday, I try to solve a problem that combines technical VBA knowledge and programming logic. Context (see attachment) On a spreadsheet, I have 5 information: Prod, Position, Name, ID and Team. The total number of lines, the number of vendors and the number of lines by...

In a VBA procedure, I’m working with shapes. As you can see in the code below, each shape has a colored background With objshp .Select .TextFrame.Characters.Text = Cells(my_row, 3).Value .Fill.ForeColor.RGB = RGB(col_red, col_green, col_blue) End With And some text is written in the...

Hello forum, I want to manipulate image files by transferring them to another folder and changing the name. See the code used. The problem is that on the instruction "ModifiedDate_MyFile MyFile.DateLastModified =" VBA responds with a message "Invalid Procedure". Could a member of the forum...

Hi the forum, I need your help to solve in VBA a problem that bothers me for some time. The problem concerns the planning of 4 employees (named from 1 to 4). Between 02 jan 2014 and 31 dec 2014, for each working day, while one employee begins at 8 am, another stays till 6 pm. The other two...

Hi the forum, A variable is defined as Static in a main procedure. To use it in another procedure, I put the name as a parameter between the parentheses after the name of the procedure in both the call and the sub. But when I try to use the static variable in a event procedure, VBA gives an...

hi the forum, Let me present a problem to your sagacity. In a spreadsheet, I display some Exif attributes of my picture files The exposure time is expressed as a fraction of a second, eg 1/30 sec, 1/100 sec. After eliminating the "sec" abbreviation, a fractional expression 1/30 or 1/100 remains...

Hello the forum, I need help to solve a problem of syntax in the SORT function in VBA. I want to sort the contents of a spreadsheet based on a criterion selected from a dropdown list. The sequence of items in the list corresponds to the sequence of column headings. So I set the column to sort by...

Hi the forum, Should some people know the VBA instruction(s) to disable the close red cross on the top right of the title bar and force the user to close the file with a button in a worksheet? Thanks in advance Harry

Hi the forum, In the Workbook_Open event in ThisWorkbook, I fill an array defined as “Public arrmsg() as variant”. A debug.print confirms that the variable “arrmsg” contains the information. Problem. When I want to use an element of this array in a procedure belonging to another module (not in...

Hi the forum, In a VBA application, I create a new worksheet on each run. In this worksheet, an ActiveX combo box is created and populated by code. How can I capture the Change event on the combo box otherwhere than in the new sheet? Thanks in advance for your answers. Harry

Hi the forum, Is there a VBA function to change the metadata of (AVI) files, for example:author, subject, category)starting from an Excel spreadsheet It is perhaps important to know that these properties, although visible, are not accessible via the properties dialog. Thanks for any advice...

Hi the forum, I have many "AVI" family files with metadata specifying the location, date, ... Is it possible in Excel VBA - Either to read the metadata directly from the hard drive and use them for selection; - Either to extract them to feed an Excel spreadsheet. Thanks in advance for any...

Hello forum, In a Win7 and Excel 2010 both 64-bits environment, I have to use in a VBA procedure a 32-bits dll. To do this, I need to compile the VBA code in 32 bits. Could someone explain to me what this means and, more importantly, how to do that. Thank you in advance for your answers. Harry

Hi the forum, I want to retrieve the absolute min temperature per day from 1833 to 2012. The date and the min are in B2 to C65384 The day numbers are in F2-F32 and the month numbers are in G1-R1 I developed a formula to analyze the data: {=SMALL(IF((LEFT(TEXT($B$2:$B$65384;"dd/mm/yyyy");2)...

Hi the forum, Could some people explain me how Excel PT react on calculated fields. I inserted an IF condition in the calculated field dialog but the results are not those which I expected. An example can be found on http://cjoint.com/?CEbk6EJ6Dzf Thanks in advance Harry

Hi the forum, In an Excel table showing the turnover by sellers (in column) and products (in line), I would like to display in red the seller with the largest total turnover without creating an additional column. Here is an example Product 1 Product 2 Product 3 Product 4 Salesman1 228...

Hi the forum, I would submit you an Excel problem or bug. In an Excel worksheet, cell B2 contains 1/1/1900, B3 = 2/1/1900 and so on. When I apply some date functions or convert it in number, the date is well 1/1/1900. But when, in VBA, I use the value of the cell it appears to be the...

Hi the forum, In a transpose function, I want to use a variable set of references. For example, instead of writing “transpose(c10:c50)”, I want to refer to cells containing the value 10 and 50. Example If A10 contains the value 10 and A11, the value 50 I wrote “=transpose(“c”&A10:”c”&A11)...