I'm using the calander control in Excel which is called in VBA when a user clicks on a cell in a range - the code is as follows:
' Calander for Activity Start Date and end dates
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, [H11:I30,H33:I52,H55:I74,H77:I96,H99:I118,H121:I140,H143:I162,H165:I184,H187:I206,H209:I228,H231:I250,H253:I272,H275:I294 ,H297:I316,H319:I338,H341:I360]) Is Nothing Then
If Intersect(Target, [K11:L30,K33:L52,K55:L74,K77:L96,K99:L118,K121:L140,K143:L162,K165:L184,K187:L206,K209:L228,K231:L250,K253:L272,K275:L294 ,K297:L316,K319:L338,K341:L360]) Is Nothing Then
Calendar1.Visible = False
Exit Sub
End If
End If...........
Let's say 1 Feb starts on week 5, 1 Mar starts on week 9 and 1 Apr starts on week 14. As such, feb has 4 weeks and march has 5 weeks. I have a spreadsheet of data on a weekly basis, and i want the data from week 5 to week 8 to be under feb and data from week 9 to week 13 to be under march. The data is to make a graph. Is is possible for excel to be linked to the calander so as to detect which month has 4 weeks and which month has 5, depending on the 1st of each month?
Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?
1. Can a Subroutine be called from within a user-defined Public Function? How?
2. Is there a difference between calling a subroutine with a 'Call Sub_Name' statement, vs. calling the subroutine with an 'Application.Run'(?? or similar) statement?
I have created a userform within VBA which has a TreeView Control and a Spreadsheet control on it.
I have populated the TreeView control with data and what I want to be able to do is to drag the nodes off the TreeView control to the spreadsheet control.
I can drag onto a normal worksheet but not onto the spreadsheet control (the no drop mouse pointer keeps showing).
How do I determine which control the user is currently modifying on a multipage form (either changing, enterying or exiting the specific control). when I use "userform1.activecontrol" i get "multipage1" as the control name but I need the actual control on the specific active multipage. (also the .TABINDEX is for the multipage regardless of the on-page control) I use a generic data-field change SUBroutine so need the control name (and the TABINDEX) to provide my SELECT CASE. (so every fieldname_CHANGE calls the same SUB [with no parameters])
I want to thank you all for the solutions/support I received in completing my (for me) difficult workbook.
I still have a tricky problem. In the example below, can Multibeep "know" when it is being called from Beep versus being excuted directly? And if so can I direct a different sequence?
I am running this code it is rather long, but I need to show it all ?
Code: Public Sub Monday() Dim mName() As Variant Dim lName() As Variant Dim fName As String
Call lower(CStr(mName(q)), fName, CStr(lName(q)))
[Code] .........
How to do is to pass the value of convert to the other procedures that are being called? As you can see I know how to pass variables down to "Sub-routines" but how would I pass the variable up?
I have a series of procedures I am calling from a function that each run fine when executed individually. These procedure are titled: DataValidation, Test1, Test2
The function will not run past Test2- I do not get the msgbox "Stage 2"
Code: Public ArrayD Public ArrayD2 As Variant Public RngD3 As Range
I am trying to sort a csv file through a VBScript. My problem is that Excel continues to run as a process on script completion. Running multiple variation of this script results in multiple instances of Excel being present in the WinXP Process Manager.
Set xlObj = CreateObject("Excel.Application") xlObj.Visible = false xlObj.Workbooks.Open "C:Test.csv" xlObj.ActiveWorkbook.ActiveSheet.Range("A1").Sort xlObj.ActiveWorkbook.ActiveSheet.Range("A1"),,,,,,,0 xlObj.ActiveWorkBook.Save xlObj.ActiveWorkBook.Close true xlObj.quit set xlObj = Nothing As a second part to my question, is there any way to modify the code to allow multiple field sorts, i.e., A1 is Primary, C1 is secondary?
I have a macro that calls functions from another workbook.
For example:
Private Sub Worksheet_Change(ByVal target As Range) If ThisWorkbook.OpenCommon <> -1 Then _ Application.Run Workbooks("common.xls").name & "!Arkusz1.CellChange", target End Sub
But when I get an error in CellChange function from "common.xls" workbook, debugger points only to the third line of the code above, not to the bad line in called function. Is there any way to change this behaviour (maybe some tool etc.)?
Private Sub Assign1Combo_Change() If Range("ComboVisible") = False Then Exit Sub Assign1_Download End Sub
Symptoms: - As soon as this is used, Excel/VBA can't select any range on the worksheet. For example, the following code (within the sub Assign1_Download) no longer works:
Range("firstdata").Select
Excel/VBA doesn't select that Named Range or any other range I try (ex. A1). - This problem only happens in Excel 2003. In Excel 2002 everything works fine (can make any selection).
I have attached a workbook with various worksheets. the aim being to create a cover worksheet called "averages".
Every few days i will manually add a new worksheet in the same format as "332" i had to delete most of the data from "333" to meet the file size limit but you should still get the idea. I would like the "averages" worksheet to work out an average rating and pull the data for all competitors from all worksheets.
I All my data is obtained from 2 columns, USERS & rating which i manually copy paste to a new worksheet every few days from that info i build my basic pivot table. maybe i can gather all the USER & rating columns on the same page and use a more complicated pivot table and thus do away with all the worksheets completely? But I do like being able to easily compare each individual rating against the other competitors for that particular day.
I have a procedure that opens workbooks from a list and then searchs for a phrase given by user. If nothing is found it closes workbook and opens next one. If phrase is found it highlights it on workbook. I wanted to give user some options when phrase is found: to search further in next workbook and to search further, but leaving current workbook open. Here my misery starts.
The only way i made up to perform it was to use Userform and code: userform.ActiveControl.Name. But it seems that Userform called in other workbook than one where the module is, interrupts somehow the procedure. When workbook, where the userform was used, closes the procedure doesn't go on. Evidently Userform is the issue here as without it, macro works.
I have a function in a cell triggered on the value returned by a DDE link in another cell.
e.g. cell A1 contains =function1(A2) and cell A2 contains =ADVFN|NYSE_CAT!CUR
function1 is triggered each time the DDE link updates regardless of whether the result returned has changed or not. How do I prevent function1 being triggered if the returned value has not changed?
Does anyone out there have a macro that will delete all worksheets except for one specific one? In this case I want all worksheets deleted except for one called "MAIN".
When I use Excel's UI to create a function, the fn is being called several times. If I enter manually there is no problem.
Add this code in any module and goto menu, Insert, Function and then select dosomething from the list and click OK. you will the msgbox is being displayed several times.
Public Function dosomething(aa As String, bb As String, cc As String, dd As String, ee As String, ff As String, gg As String, hh As String) MsgBox "a" End Function
I'm trying to replicate the same functionality using Excel Analyzer that Lotus 123 had with it's function called Backsolver.
Below is a simple matirx of expenses, that shows a sum total of 3297. I want to change the number 3297 to 5000 and I want the rows and columns to add up to that total based on the same % relationship that existed when this matrix added to 3297
This technique is useful when you are trying to spread changes in a plan and you need to develop placeholders that add up and accross to the new target i.e, 5000 based on the previous targets % relationships that existed when the row and columns added up to 3297
I can use the analyzer, but when it finds its solution it returns negative values as part of the solution. I'm missing something in trying to set the parameters so that it won't return me negative #'s.
i need to create a function macro Called FSTD that inserts words into a cell and conditionally formats them depending on th number in the cell on the left.
in the sheet there are ranges that the number will fall between.
Example
Cell B1 is the cell were i want to run the function in Cell A1 is the cell with the number that is used in the function
Cells A3:A7 are that ranges that i need to use in the function
E.g. if the number in A1 is less than A6 = i want "P" to appear in the cell with a red backround
if the number is greater than A6 but less than A5 = i want "M" to apear with an orange back round
if the number is Greater than A5 but less than A4 = i want "E" to apear with a green backround
even if it is possible to do without changing the backrounds, just to insert the letters
I am trying to search in a new workbook called RepricingFactors. The worksheet within that workbook is called Network Differences. I am searching for the city Louisville out of about 100 cities. Do I have this set up correctly???
I have written a user-defined function that searches for a small range within a larger range. The function requires two input parameters: the range you are looking for, and the range you want to look within.
For example, I might look for the string of values in cells A1:D1 in a larger range E1:H20. The function returns the row number in the larger range where the smaller range is found.
My problem is this: The function is working fine when I call it from another sub procedure. However, when I try to run it as a worksheet function, I get a "#VALUE!" error. The function pops up in the "insert function" menu, and it prompts for the two input parameters.
I call it from a macro. It is supposed to write some temporary files, and close them in the end, and delete them. Sometimes, the dll can't close properly, and the text files remain open.
With error treatment in my macro, I can continue execution, but i need to delete that files.
Since the dll DOES NOT CLOSE the files, i'm not able to delete them in my macro.
I need some way to close that files, in order to delete them.
When I use GetFile to reference the file, it remains as "File", which does not accept "Close" method. I can reference the file with myfile.OpenAsTextStream, but this way it seems to me that the file is opened again, and the "Close" method does not work either, with the file defined as a TextStream object.
If I close excel, I'm able to delete those files, but that's not the way I want. Since IT'S OPEN IN EXCEL PROCESS, I imagine there's some way to close it.
I am writing an application in Excel 2007 VBA (on Windows Vista) which presents the user with a series of 3 userforms, each containing a listbox. The user clicks on an item in the list box of the first form and then I use the listbox click event to process the answer and assemble the list items for the listbox in the second form. This sequence is repeated in the second form (user clicks item in listbox, click event processed and used to generate 3rd userform/listbox). This seemed to work fine at first but now in some cases after clicking an item in the first listbox, I am presented with the third listbox. Apparently a choice was made in the second list box and the third list box created based on that choice but I never was presented with the second userform/listbox.
I don't think it is related to the mechanics of clicking the mouse because it occurs repeatably as follows: I had a list (for listbox 2) that was 3 items long and everything was working. I added a fourth item. Now it consistently chooses the 3rd item in this list without ever presenting me the userform/listbox 2. If I remove the fourth item it presents me with userform/listbox 2 and I click on an item and everything works as intended.
The code for the second listbox callback is very short and simple:
Private Sub SecondListBox_Click()
Dim choice As String
choice = SecondListBox.Value Unload Me MakeThirdListBox (choice)
