Re: File Not Found Error
How big is the xla? I have one that's 3 megs and it seems to work ok.
Re: File Not Found Error
How big is the xla? I have one that's 3 megs and it seems to work ok.
Re: File Not Found Error
Try changing the IsAddIn property of the workbook to False?
Not sure, have never seen this.
Re: File Not Found Error
Sounds like one of those wierd errors! If you can't find the xls version, try saving the xla as a xls again? Not sure, since you can't attach it due to the size. Try inserting a module into the xla, setting a breakpoint and stepping through the code, this has helped me get to code before.
Re: Input Message Position
You can't position a message box. You could use a userform instead. You can position a userform. Put a text box and a command button on the Userform to simulate the message box.
Re: Copy A Worksheet In Current Workbook
Can you just insert a new worksheet, go to the one you want to copy; copy the whole sheet and then paste it into the new worksheet?
Highlight the whole worksheet, copy. Switch to the new worksheet, select cell A1 and then paste.
Re: File Not Found Error
I'm assumming the add in is saved as an "*.xla" file.
You could try making an excel file and add a vba form or module then try to open the xla file. You should be able to see all the code / projects in the vbe then.
Re: Percentage Of Points Within Range Of Target
What's a point? You may want to provide an example of what you're talking about.
Re: Copy Cells On Close
What about the workbook Before Close event?
Re: Transfering Relevant Data From A Worksheet Into New Book
Do you mean worksheets or workbooks?
Do you want to search three separate workbooks for a specific term and then copy or cut that term and it's associated row of data and put it into another workbook/worksheet. Also, where in the new book/sheet do you want it, in the 1st row?
Re: Hidden Cells
perhaps if you could give a concrete example of what you're doing I could be of more help.
but basically, you can't completely protect hidden cells on a worksheet; you can protect vba code.
Re: Hidden Cells
I wouldn't protect the rows/columns in vba. I would put the data I needed in vba and then protect the vba project with a password.
I'm wondering if you need the data in a hidden column, if you could use it from vba and therefore protect it?
Re: Hidden Cells
I know you can put things into vba and then protect it. Not sure if you could put it on another worksheet and then protect it? I know that hidden columns are not secure, i.e., there is no real way of hiding them completely.
Re: Finding Something On A Spreadsheet And Inserting A Line Afterwards
Try this:
Dim x As Integer
Dim y As Integer
For y = 1 To 5
For x = 1 To 7
If Cells(x, y).Value = "2800" Then
Cells(x, y).Select
Selection.Insert Shift:=xlDown
Cells(x + 2, y).Select
End If
Next x
Next y
Display More
The x loops through the rows and the y loops through the columns.
If "2800" is found in any row or column a row is inserted and the search is continued below the found item. i.e, "2800"
Re: Macro To Concatenate Columns
Try this or something like it. You can find the lastrow and substitute that also in the for loop.
Dim x As Integer
For x = 1 To 6
Cells(x, 3).Value = Cells(x, 1).Value & " " & Cells(x, 2).Value
If InStr(Cells(x, 3), "_") > 0 Then
Cells(x, 3).Value = Left(Cells(x, 3), (InStr(Cells(x, 3), "_")) - 1)
End If
Next x
Put it inside a sub.
The value in Cells(x,3) "Col C" will be Cells(x,1) "Col A" and a space " " and Cells(x,2) "Col B"
The for loop lets you cycle through each row.
The InStr function shows the position of the _ in the string and the Left function displays the results minus 1 "the underscore".
I have a large excel-vba file, it's 9 megs. I decided to put the userforms and the modules in the form of an xla.
I seem to be able to access the code in the modules (subs and functions) but am unable to access the userforms directly from a workbook.
The question is: if you have an xla that contains userforms, how do you reference them from vba code in another workbook?
I tried both:
I have a reference to the xla set in the vba project.
any ideas?
Re: Variables In For Loops
Yes, I'm trying to clear the contents of the combo boxes. That code didn't work either - I don't think the combo box is a shape?
I tried:
Worksheets("Bottles").Activate
Dim ControlName As String
ControlName = "cboPrtStatBott"
For x = 1 To 10
ActiveSheet.Shapes("Controlname" & x).Value = ""
Next x
It wasn't able to find the control.
Re: Variables In For Loops
Thanks for the help. I tried this:
Dim ControlName as String
For x = 1 To 10
ControlName = "cboPrtStatBott" & x
Worksheets("Bottles").ControlName.Clear
Next x
but it didn't work either.
I have numerous worksheet combo boxes. Some of them have the "root" name in common and are distinguished by a number. e.g. cboPrtStatBott1, cboPrtStatBott2, cboPrtStatBott3, etc.
One of the functions I want to perform is to clear the combo box before I add new data to it, hence the line:
Worksheets("Bottles").cboPrtStatBott1.Clear
This line will do what I need it to do. The question is how can I do this in a For Loop so that I can cycle through all of the changes I need to make on all of the combo boxes.
I hope this helps clarify what I'm looking for.
Is it possible to use a variable to change a controls name in a For Loop?
I have several worksheet combo boxes that have the same root name, e.g., cbobox1, cbobox2. cbobox3, etc. I want to be able to keep the main name and change the numeric part in a for loop and also perform some function with the control.
For example:
This gives the basic idea. How do I make cbobox(x) evaluate correctly?