The spreadsheet is essentially a VBA GUI that validates information entered before writing it to a sheet in the workbook. There is a button on the first sheet that opens the GUI, and when the workbook is first used the GUI opens and runs fine. After adding a few rows using the GUI, saving the workbook, and then reopening it, attempting to open the GUI by clicking on the button will cause Excel to crash (and no errors are given). To compound the problem, it is not possible to find the issue by using the debugger, as the GUI runs fine as soon as VisualBasic is opened. I've tried to narrow it down by using MsgBoxes to find the approximate location where the form crashes, and it seems to happen when the .ListIndex property of a ComboBox is set inside the UserForm_Initialise method. I've played around for days trying to narrow it down further, removing .ListIndex statements as much as possible without breaking the entire thing.
I have two comboboxes on a userform, they both get there list from the same formula. What I am trying to do is have the second combobox have it's selection preset based on the selection in combobox 1.
ie Combobox1 = 6:00 AM when you click on the dropdown for combobox2 i would like 6:00 AM to be the first selection possible, but I dont want it displayed in the box unless it is selected.
Dim timdat1(1 To 85) For i = 1 To 85 timdat1(i) = Format(TimeSerial(5, (i + 1) * 15, 0) - Int(TimeSerial(5, (i + 1) * 15, 0)), "h:mm AM/PM") Next i combobox1.List = timdat1 combobox2.List = timdat1 i am at a loss for where to go from here
I have a userform that has a text box. If user puts a number in it and click on proceed the userform must expand and display that many comboboxes. for e.g. if user inputs 8 and then click on proceed then there should be 8 comboboxes on the form. Is it possible to do?
I have a worksheet "Master Log" with a UserForm "UserForm2" set up that has 4 comboboxes and 2 text entries. I already have the filtered unique values for each combobox sent to columns "O" thru "R" and they are dynamic ranges. I have the code to populate one of the comboboxes in the userform but cant figure out how to modify the code to have all four populated without getting ambiguous entry errors. And also, do I have to initiate the form for each combobox? Below is the code Im using for a single combobox.
Private Sub UserForm_Initialize() Dim MyUniqueList As Variant, i As Long With Me.ListBox1 .Clear ' clear the listbox content MyUniqueList = UniqueItemList(Range("o4:o100"), True) For i = 1 To UBound(MyUniqueList) .AddItem MyUniqueList(i) Next i .ListIndex = 0 ' select the first item End With End Sub
Private Function UniqueItemList(InputRange As Range, _ HorizontalList As Boolean) As Variant Dim cl As Range, cUnique As New Collection, i As Long, uList() As Variant Application.Volatile On Error Resume Next For Each cl In InputRange If cl.Formula <> "" Then cUnique.Add cl.Value, CStr(cl.Value) End If Next cl UniqueItemList = "" If cUnique.Count > 0 Then Redim uList(1 To cUnique.Count) For i = 1 To cUnique.Count uList(i) = cUnique(i) Next i UniqueItemList = uList If Not HorizontalList Then UniqueItemList = _ Application.WorksheetFunction.Transpose(UniqueItemList) End If End If On Error Goto 0 End Function
I Have a sheet with 4 activex comboboxes and 3 text boxes. If the right item is selected in the second combo box a user form opens up. That has 4 text boxes. It has a command button titled ok that takes the information from the 4 text boxes and puts them in a sheet called data.
Code: Private Sub cmbOK_Click() With Worksheets("Data").Range("A1") .Offset(1, 8).Value = Me.txtFirm.Value
[Code]....
I need a way for the above code to run when the ok button on the user form is clicked.
I have three ComboBoxes. I need the choice of the first ComboBox to detirmine what is shown in the second and the choice in the second to detirmine what is shown in the third. The lists will be growing as users add items.
This example workbook contains a datatable, which is inputted via a userform. The datatable has 4 columns: Date, Invoice no., Loads, Tonnage. This table is dynamic, as a new row gets entered each time data is entered in the userform. A different userform (the one in the example) has comboboxes which refer to the data in the table. This userform asks the user for the Date, Invoice no., Loads and Tonnage. I want to use comboboxes so that they will advise the input based on the users previous input.
The first combobox asks the user for a data, and should contain a list of all the unique dates that are stored in the table. When the first combobox is inputted, the list for the second combobox will change. The list of the second combobox should be a list of unique invoice numbers, based on the date that has already been entered. The 3rd and the 4th combobox should also show a list of unique values, based on the previously entered date and invoice no.
Example: The first combobox should advise the dates: 4-Nov-14 and 15-Nov-14. User chooses 4-Nov --> second combobox should advise unique invoice numbers based on chosen date: 1252 and 1311. User chooses 1152 --> 3rd combobox should advise unique values based on previous values: 3, 8 and 7. 4th combobox: 57, 23 and 47.
I created a UDF at home (Excel 2003). In my workbook I set the calculate option to manual. When I utilized the UDF, everything worked fine and the result displayed in the cell. Move forward 10 hours. I bring the workbook to work (also Excel 2003). Now when I utilize the UDF, I get "#Value!" in the cell. This stays until I press F9 and recalculate. After I press F9, the correct result is displayed in the cell.
The weird part is: If I turn the calc option back to auto, the correct result is displayed each and every time. I switch back to manual and #Value! appears until I press F9.
Demarcating an Unusual Series to club alike elements.
First and Foremost ...
Happy Diwali to everyone.
May this year bring peace and happiness to the globe less of terrorism and war-like situation..
Now back to the query...
I need to not only count the no of cheques used for a particular account but also understand to which Cheque Book they Belong to..
I have some cheque mentioned one below the other not neccessarily in series but they need to be clubbed to Series 1 and if not alike then the next Series i.e. Series 2 so on so forth..
I have atttached the file which can explain in better manner the exact requirement..
Please someone help me on this as I have burned enough midnight oil with no futile gains till now.
I made an Excel/VBA solution for a company in which userforms were created and manipulated programatically, including their code modules. They would then be deleted so next time they were created there wasn't naming errors.
They acted as data input forms which would have an input control for each column of data on the sheet.
There was one method which would create the form and then call another to add code to it's code module.
In one scenario, if the user would do data entry on one sheet and then immediately after do data entry on the same sheet using the data entry forms.
When this occurred something peculiar happened, after the method which added the code to the code module executed excel would crash. By putting a MsgBox to pop up just before the End Sub statement and having another in the calling function immediately after the procedure call. It became evident that the sub adding code to the module was not exiting correctly and causing the program to crash.
In essence the program was crashing when End Sub was executed
My Excel file crashes whenever I attempt to make a copy of a tab, within the same workbook, and then save. This problem only occurs with its existing naming convention. In other words, the file crashes with the name "Cash Flow Model 5 Year Plan". If I rename the file to "Copy of Cash Flow Model 5 Year Plan" then it works perfectly, with no issues copying a tab within the workbook. The file size is not too large, as this issue never occurred when the model was twice the size.
I've been putting together an automated spreadsheet that has external data sources to SQL. I have been having some strange problems with it between versions of Excel. Unfortunately, the company's standard is 2003 but quite a lot of the company use 2007. I have been developing this report on 2007, the server that runs the automated version of the spreadsheet runs 2003 (and cannot be upgraded beyond 2003 as it is running Server 2000!).
The template opens fine on my 2007 machine and all development work has been run on my machine including the downloading of data. The VBA script runs something like
I have to send out emails to all of my staff with their new login ID's and PW's and other misc information one by one to each user. Reason being is that all ID's, PW's and other information pertains to that specific user and includes sensitive information.
I know there's a VBA script I can use to send out the emails, but the script I found online keeps crashing my Outlook and requires it to go through my personal inbox which takes 15 minutes every time.
I have the pertinent information split up into different columns/cells.
A1: email A2: subject A3: body (ID, PW, verbage) A4: attachment link (if required) A5: if I could have Outlook automatically stamp each email with my signature that'd be awesome as well.
error when I try to close the spreadsheet. One of the sheets can have a lot of data (up to 10,000 x 150) and the error will also occur if I try and delete the sheet, or clear the entire sheet. Also, the model runs vba code, which at one point deletes the data sheet and adds a new sheet. However, sometimes the sheets.add functionality stop working, i.e. when the code runs the sheets.add line, nothing happens. I don't know if the errors are related.
I have a semi-large macro which I have been creating on and off for a few months now. The file will fundementally be used by people with little knowledge of Excel, hence I force the user down certain alleyways depending on what they have previously done with the aid of veryhidden sheets and such like.
Upon opening the file, some code is ran to hide all of the sheets in the file apart from the home page. This is a pain when I am still working on the code though, so a portion of it is commented out. When I uncomment the "on error.. - next sheetIn" so that the file runs as it would be used, the code itself works perfectly and hides all of the sheets apart from the home page. Unfortunately, I can't then view the VBA code as it instantly crashes Excel (and any other Excel file I currently have open). This happens whether I click the Visual Basic button in the Excel ribbon, whether I try and access it through the design mode on a control, pressing Alt+F11 or even opening up another spreadsheet containing code and attempting to click on the broken macro after viewing the VBA code for the working spreadsheet.
VB: Sub workbook_open() 'stop screen flickering whilst running the code Application.ScreenUpdating = False
[Code]....
The only way currently to get back into the code is to open the spreadsheet up with macros disabled and recomment the code, but obviously this loses the function of the code.
I have a report that uses external data to feed a Pivot Table.
I noticed that the data source had been turned off automatically by Excel and turned it on and added the file location to the trust centre.
I also set the data to refresh when the workbook is opened.
I saved the file and then tried to refresh the data which caused Excel to crash. Of course now I can't open the file either because when it attempts to refresh itself on opening that causes Excel to crash as well.
Addendum: I have removed the Trusted Location which has caused Excel to disable the connection again. This has solved the crash on opening but I still can't refresh the data.
I call the code into a Worksheet_Change module on a particular sheet (Sheet1). It hides rows between 11 to 1503, but if I innocently select these rows, the spreadsheet freezes on me & I have to kill Excel. I'm guessing I need to add code to counter these do nothing events?
I've been trying to put together a function in excel which will make it simpler and a bit clearer to produce the coefficients of trend lines in cells. I want to produce an excel function like SLOPE and INTERCEPT but for exponential, power and logarithmic trendlines. For example, I can produce the coefficients for an exponential trendline like this:
Ideally, I want to be able to do so without the need to convert the terms with LN function, and be able to replace it with a new function that deals directly with an X Range and a Y range
I've produced the following code:
Code: Function PowerSlope(YRange As Range, XRange As Range)
For Loops = 1 To YRange.Count YRange.Value2(Loops, 1) = Log(YRange.Value2(Loops, 1)) Next Loops
PowerSlope = Application.WorksheetFunction.Slope(YRange, XRange) End Function
However, this causes Excel to crash and shut down (not the macro itself, but the entire program). I can't even get the function to run to a break point in the first line before the crash happens. I'm able to remove the FOR ... NEXT loop and use the function to directly return the result of the linear slope, so I'm confident the issue is not in that part of the code.
I'm building a bookkeeping workbook that is only currently 800kb with TWO cells that have data validation and no cells have conditional formatting. There are a fair few formulae but they are all simply SUM, SUMIF and CONCATENATE.
I have a genuine copy of office pro 2010. All works fine except excel!
It seems to be mainly on one workbook I have brought over from open office and on a mac! It wont save due to errors it won't tell you about? It then stops responding or crashes? Wont save as etc....
I have repaired, I have even deleted and downloaded a new office pro genuine and installed and doing the same so i guess it has to be the file?
BUT it seems to work on another laptop we have, same file, same excel????
The only thing i seem to be able to find is in the permissions it has an "unknown account"? Also under the file menu where it allows you to check the file it states something about conditional formatting i have used and not being compatible BUT i cant for the life of me find that in the menus to get rid?
I recently received an .xls book which I then saved as .xlsx (I'm using 2010). There are just under 8,000 rows and 20 columns. File Size 1MB.
The only formulas in the sheet are the ones in a column which I inserted and copied down for all 8,000 rows. Nothing too complicated: no arrays or anything. The sheet calculates fine.
I am simply trying to copy and paste these formulas as values (into the same cells), though at every attempt Excel crashes. I tried on smaller sets of the column and just got it to work for a few hundred rows, though it struggles with any more than that.
I opened a different workbook of mine, and tried the same operation on twice as many cells containing complicated, lengthy array formulas and the action completed instantly.
There is no Conditional Formatting in the book, no code, no 'last cell' issue, no Named Ranges, no external links.
I have even copied the data to a new workbook, then copied the text of just one of the formulas over into this book, added an equals sign, copied down and recalculated, then tried to paste as values again. Still crashes.
is far more resource-hungry than I thought, though if that were the case, wouldn't the issue be during calculation (which, as I said, is fine) and not during a paste attempt? No, it can't be this.
I have one simple (but large table). It has dates across the top (formatted in hh format). I would like the associated table to format according to the day and also to format differently when there is a public holiday. So I have built a table with the holidays and named the relevant cells as "Holiday_Valid".
I have the following formula in the conditional formatting;
[Code]....
Where D11 has the current day in question and Holiday_Valid is a list of public holidays. Since there is a cell for each hour of each day I am using "int()".
There is a second conditional format to format Sundays differently as below;
[Code] .....
My problem is that these both work well....but then after a few minutes the whole sheet crashes with those dreaded "Trying to recover your data" and "Excel will restart" etc.
I have removed references to named ranges and so far - so good....but this means putting the validation table in the same sheet as the main table. In the past I have been able to use named ranges (albeit not in such convoluted formulae), but now it seems that it is not working any longer.
When I open the recovered sheet, all the conditional formatting has been removed and the message from the repairs is that there was some invalid conditional formatting.
Extensive web searches did show some issues with conditional formatting using names ranges....especially with frozen panes....which I need use with a sheet this big.
I'm thinking of writing a small tool to store user information on certain files.
The user data will be input by means of comboboxes on a userform.
Each combobox will need to contain a list of values previously used for that combobox. (I'm planning to use a separate ini file for each combobox)
All of the user data entered for a file will be saved into a single ini file.
Most of this is straightforward however I'm not good with comboboxes. And working this in with Ini files makes it harder!
I'm struggling with the following concepts:
SOLVED - How do to make a combobox populate with all the values from its ini file SOLVED - How to identify if a selected value in any combobox is new (i.e. wasn't in the original list presented to the user) SOLVED - (From above) How to write this new value to that comboboxes ini file (so it will appear in future lists for that combobox) How to input two values from any given combobox (From above) How to record two values for one field into the files ini file.
I'm sure this must have been answered before but I cant find it, sorry.....
I have 12 comboboxes on a worksheet. They are normally filled out by typing the first 3 characters of one of the entries in the list they are populated with.
I.e. the list starts;
AAC - Al Arish - Egypt - HEAR AAN - Al Ain - United Arab Emirates - OMAN AAW - Abottabad - Pakistan -
The first 3 character are unique to each line.
I'd like to be able to enter the 3 characters in the first combobox & then tab to the 2nd combobox, etc, etc. No problem in a userform but how on earth do I do that when the comboboxes are on the worksheet?
I am using the following code to determine whether a given range is the linked cell for a Combo Box by looping through the shapes collection.
Function LocateFormControl(OverRange As Range) As Shape Dim objTemp As Shape For Each objTemp In OverRange.Parent.Shapes If Left(objTemp.name, 6) = "Drop D" Then If WorksheetFunction.Substitute(objTemp.ControlFormat.linkedcell, "$", "") = WorksheetFunction.Substitute(OverRange.Address, "$", "") Then Set LocateFormControl = objTemp Exit Function End If End If Next Set LocateFormControl = Nothing End Function
However, when I use this code on big worksheets with many other shape objects (such as Comments), the program runs very slowly. Is there a ComboBoxes Collection that I could use to avoid looping through all shapes on the worksheet, or is there a different, faster way to run this code?
1. population a combobox in excel vba using code as opposed from a sheet 2. population a listboxin excel vba using code as opposed from a sheet 3. population a combobox in excel vba using text from a file as opposed from a sheet 4. Getting the values from a combobox on a form to populate cells on a worksheet
These list boxes and comboboxes will be on a form.
