Re: $$Excel-Macros$$ Cannot display properties of ActiveX checkbox that has been added through VBA
On Oct 12, 2011 Stuart Redmann wrote: Hello newsgroup, I tried the following code to create an ActiveX checkbox under Excel: - Create new workbook. - Add following code to worksheet 1 Sub test() Me.Shapes.AddOLEObject Forms.CheckBox.1, , False, False, , , , 100, 100, 100, 100 End Sub - Execute code Now I have got a worksheet with a single checkbox. If I try to view the properties of this checkbox, I enable the design mode of the Toolbox toolbar and make a right-click on the checkbox. The options that are available are Cut, Copy, Paste, separator, Checkbox-Object, Show graphics symbol bar, separator, Grouping, Order, separator, Assign Macro (greyed out) and Format object (these are my translation of the German version of Excel, so these menu entries may be different in the English version). I miss the entry Properties. However, if I add a checkbox by hand, I can display the properties of this checkbox through the context menu. Once the property window is up, I can select the VBA added checkbox and can edit its properties without problems. I use Excel 2003 (11.8231.8221) SP3. Can anybody re-produce this behaviour? Is this a bug? If it is a bug, does it go away under Excel 2003+x, x0? On 12 Okt., rajan verma wrote: Dont Use Shapes , use this Code ActiveSheet.OLEObjects.Add(classtype:=Forms.Checkbox.1) Thank you for your answer. Unfortunately, this yields the same problem on my computer: Even if I add the control in the OLEObjects collection, I still can't see the entry properties in the context menu of the newly added control. It looks as if something really weird is going on here. Could you please try to add a control on your computer and look whether you have a proper context menu for this control? I'd be very obliged. Thanks in advance, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Cannot display properties of ActiveX checkbox that has been added through VBA
Hello newsgroup, I tried the following code to create an ActiveX checkbox under Excel: - Create new workbook. - Add following code to worksheet 1 Sub test() Me.Shapes.AddOLEObject Forms.CheckBox.1, , False, False, , , , 100, 100, 100, 100 End Sub - Execute code Now I have got a worksheet with a single checkbox. If I try to view the properties of this checkbox, I enable the design mode of the Toolbox toolbar and make a right-click on the checkbox. The options that are available are Cut, Copy, Paste, separator, Checkbox-Object, Show graphics symbol bar, separator, Grouping, Order, separator, Assign Macro (greyed out) and Format object (these are my translation of the German version of Excel, so these menu entries may be different in the English version). I miss the entry Properties. However, if I add a checkbox by hand, I can display the properties of this checkbox through the context menu. Once the property window is up, I can select the VBA added checkbox and can edit its properties without problems. I use Excel 2003 (11.8231.8221) SP3. Can anybody re-produce this behaviour? Is this a bug? If it is a bug, does it go away under Excel 2003+x, x0? Thanks in advance, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: How to recast a variant as an array in VBA
On 7 Sep., ShayneH wrote: Is there a way to expose an array that is enclosed by a variant? Basically I have one function that returns a variant type which needs to be passed to another procedure that requires the parameter to be typed as a byte array. eg: Function UsuallyReturnsAnArray() As Variant the result, after testing to be an array is passed to: Sub ConsumesByteArray(ByRef ByteArray() As Byte) I need to find a way to coerce the variant that contains a byte array returned from the first function to a byte array. Copying each element to a dimensioned array is a possible solution, but it seems to me there must be a way to reference the array that is contained inside the variant type. I don't see your problem: Function getArray() As Variant Dim h(5) As Double getArray = h End Function Sub test() Dim v As Variant v = getArray Dim h() As Double h = v End Sub Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: How to add a name column to scatter chart's data label?
On 6 Aug., Excel 009 wrote: Hi All, I have the following data and want to create a scatter chart. I am using either 2003 or 2007. Person X1 X2 -- --- --- a2 4 b8 2 c9 3 d1 5 e8.51 (The range is =Sheet1!$A$1:$C$6) I want to put the values (a, b, c, d, e) from the Person column next to the data point. For example, d for the (1,5). Can anyone help? Excel 009 You can do the following workaround: Make the chart an ordinary XYScatter chart and add one series for each data point: Sub CreateChart() If Selection.Columns.Count 3 Then MsgBox You have to select exactly 3 columns to create the chart. Exit Sub End If Dim NewChart As Excel.Chart Set NewChart = ActiveSheet.ChartObjects.Add(100, 100, 100, 100).Chart NewChart.ChartType = xlXYScatter ' Add the series to the chart: Each row of the selection will end up as one series. Dim CurrentRow As Long For CurrentRow = 0 To Selection.Rows.Count - 1 Dim NewSeries As Excel.Series Set NewSeries = NewChart.SeriesCollection.NewSeries NewSeries.ChartType = xlXYScatter Dim TempString As String TempString = =SERIES( ActiveSheet.Name !R (Selection.Row + CurrentRow) C (Selection.Column + 0) , TempString = TempString ActiveSheet.Name !R (Selection.Row + CurrentRow) C (Selection.Column + 1) , TempString = TempString ActiveSheet.Name !R (Selection.Row + CurrentRow) C (Selection.Column + 2) , 1) NewSeries.FormulaR1C1 = TempString '' ' Customize your series here. ' ' Set the attributes of the series: marker attributes, labels, and so on. NewSeries.MarkerStyle = xlMarkerStyleCircle NewSeries.MarkerBackgroundColor = 1 NewSeries.MarkerForegroundColor = 1 NewSeries.MarkerSize = 5 NewSeries.ApplyDataLabels AutoText:=True, _ LegendKey:=False, _ ShowSeriesName:=True, _ ShowCategoryName:=False, _ ShowValue:=False, _ ShowPercentage:=False, _ ShowBubbleSize:=False Next CurrentRow End Sub The problem is that you'll have to set any attributes of this pseudo- series through VBA code (the macro recorder will help you most of the time). Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
Re: $$Excel-Macros$$ Re: How to add a name column to scatter chart's data label?
On 6 Aug., Excel 009 wrote: I have the following data and want to create a scatter chart. I am using either 2003 or 2007. Person X1 X2 -- --- --- a2 4 b8 2 c9 3 d1 5 e8.51 I want to put the values (a, b, c, d, e) from the Person column next to the data point. For example, d for the (1,5). On August 09, Stuart Redmann wrote: You can do the following workaround: Make the chart an ordinary XYScatter chart and add one series for each data point: Sub CreateChart() [snip] On 9 Aug., Rajan_Verma wrote: I don't have a Very Good Experience with Charting with VBA and I have tried this Macro Exist in trail mail, its giving an error Series Order Must be An Integer 0 to 255 , Can anybody explain this Most probably you have hit the limit of 255 series for a chart, since my macro tries to create a series for each row of the selected range. If this is a real problem, we would have to change the code in such a way that more multiple charts are created that are placed at the same location. However, I tried to create a chart with 400 series and the error message I am getting is more cryptic Run-time Error 1004. So probably you have done something else. Could you post an example worksheet that shows this problem? Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: VBA macro runs fine, but freezes if I try to do ANYTHING else while it's running
On 3 Aug., Rruffpaw wrote: I have a very large Excel spreadsheet (13 MB) with several macros. One of them runs a loop with multiple commands. The macro runs fine, as long as I sit back and just watch it run. If I click on another window however, it invariably freezes and I have to terminate Excel. Any ideas on how I can remedy this? I'm running Excel 2003 on Windows XP, but have noticed the same problem with the same spreadsheet on a machine running Excel 2007, also on Windows XP. Most probably this is caused by improper programming: Many macros (especially those that have been recorded with the macro recorder) will only work properly if the user does not interfere with its execution. The reason for this is that many macros do not refer to some selection directly but use two steps: first activate the desired cells, then work on the current selection. This is fine as long as no one messes around with the current selection (IOW, you ;-) A macro that is programmed properly will of course never use this technique (the macro recorder can not do it any other way than the current selection, that's just the way it is designed), so that the user would be free to go through the workbook without messing up the macro execution (note that the code would also have to call DoEvents from time to time or else the UI stays frozen). If you want to find out what has happened when Excel appears to have frozen, simply press Escape (you may have to hold it for 2 or 3 seconds). This will pause the execution of the macro so that you can see what it is doing in the debugger. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: remove duplicates - is it a good code?
On 1 Aug., bpascal123 wrote: Hi all Experts, Below is the code I produced following my learning of vba for Excel from excelvbasql.com. I would like to know if there is anything to be done about this code. I'm looking to learn from anyone who can share his/her experience. Cheers, The code shows multiple deficiencies. I'll mark them at the places where they appear. Option Explicit Sub DeleteDupl2() Why does your sub not take any parameters? I'd have expected that it would at least need to know from which Excel.Range it should remove the duplicates. Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Dim wb As Workbook Dim ws As Worksheet Set wb = ThisWorkbook Set ws = wb.Worksheets(sheet1) A sheet named sheet1 may not exist. Your code does not deal with this gracefully. Dim lastr As Long, lastc As Long Dim i As Integer, j As Integer Dim icopy As Integer This variable declaration style looks a lot like C. It is quite confusing. Why don't you declare the variables at the places where you actually use them? Dim Table() As Variant 'Array for all values Dim TableOK() As Variant 'Array for unique values The variable names are quite non-descriptive. Instead of Table you should name them UnfilteredData or OriginalValues or ValuesWithDuplicates. The same goes for TableOK. Dim iOkSize As Integer, jOkSize As Integer 'Rows and column size for TableOK Dim iOK As Integer, jOk As Integer Dim idD As String 'to concatenate all values in a rows Dim idj As Integer 'loop variable to concatenate idD string Dim deleteSt As String deleteSt = ---%%% lastr = ws.Cells(Rows.Count, 1).End(xlUp).Row lastc = ws.Range(A1).End(xlToRight).Column ReDim Table(lastr - 2, lastc) If you invoke this sub on an empty sheet, you'll try to ReDim Table to (-1, 256), which of course does not work. Furthermore, lastc will be 256 if there is only data in first column of the sheet. Have a look at Worksheet.UsedRange, this is probably what you want. But keep in mind that DeleteDupl2 should work on a range that is passed as input parameter. Then you should add Subs like DeleteDupl2ForSelection and DeleteDupl2ForUsedRange that can be launched via Execute Macro and launch DeleteDupl2 with the appropriate parameter. ReDim TableOK(0, 0) '''Copy data into Array Table '''Concatenate column values into an addito For i = 2 To lastr For j = 0 To lastc - 1 Table(i - 2, j) = ws.Cells(i, j + 1) Next j For idj = 1 To lastc idD = CStr(idD) CStr(ws.Cells(i, idj)) Next idj Table(i - 2, j) = CStr(idD) idD = Next i '''mark duplicates but keep untouched the original value For i = 0 To lastr - 2 j = lastc idD = Table(i, j) For j = i + 1 To lastr - 2 If CStr(Table(j, lastc)) = CStr(idD) And Right(CStr(Table(j, lastc)), 6) ---%%% Then Table(j, lastc) = CStr(Table(j, lastc)) ---%%% End If Next j Next i Range(ws.Cells(2, 1), ws.Cells(lastr, lastc)).ClearContents '''Count unique values in Table For i = 0 To lastr - 2 If CStr(Right(Table(i, lastc), 6)) deleteSt Then iOkSize = iOkSize + 1 End If Next i iOkSize = iOkSize - 1 jOkSize = lastc ReDim TableOK(iOkSize, jOkSize) ''Copy unique values into TableOK For i = 0 To lastr - 2 If CStr(Right(Table(i, lastc), 6)) deleteSt Then For j = 0 To lastc TableOK(iOK, jOk) = Table(i, j) jOk = jOk + 1 Next j iOK = iOK + 1 jOk = 0 End If Next i ''Copy unique values in worksheet For i = 0 To iOkSize For j = 0 To jOkSize - 1 ws.Cells(i + 2, j + 1) = TableOK(i, j) Next j Next i Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Your algorithm works OK, but as you have noted, it is quite slow. There are sevaral ways to make it faster: (A) Replace the loops that read and write the data into the arrays by a read operation that reads more than one cell at a time. You'll have to work with VARIANT arrays, though, as this is what Excel uses internally. (B) Don't use strings as key for your array. Strings are quite slow. Besides you'll get into trouble if you don't use some delimiting character: With your current implementation the cells aa | bb will receive the same key as the cells a | abb, so that your algorithm will detect a false duplicate. Note that if you compare the contents of the cells as strings, you'll be much slower when the cell actually contains a number that has already been parsed by Excel. If you compare the numbers, you'll be a bit faster (you'll have to measure which
$$Excel-Macros$$ Re: VB Help needed.
On 12 Mai, Born to Win wrote: Hi Experts, in attached excel file you can see contact no. along with many services activated on a single no. you can find it in separate entry i am trying to write a VB program so that i would have unique no. services (column wise) in new sheet in one column after a single click. please help me out Thanks, prabhat The following does the same as the code in the first answer, only much faster. Sub PrintServiceColumnWise2() ' Select the contents of the sheet Base into an array for ' further processing (moving through an array is less expensive ' than working with the cells of the worksheet directly). Dim SourceSheet As Excel.Worksheet Set SourceSheet = Worksheets(Base) Dim SourceCell As Excel.Range Set SourceCell = SourceSheet.Range( _ SourceSheet.Cells(2, 1), _ SourceSheet.Cells.SpecialCells(xlCellTypeLastCell)) Dim SourceArray As Variant SourceArray = SourceCell.Value ReDim TargetArray(UBound(SourceArray, 1), 10) As Variant ' Go through the first column in the source sheet until we find ' an empty cell. Dim TargetRow As Long TargetRow = -1 Dim TargetColumn As Long TargetColumn = 0 Dim SourceRow As Long For SourceRow = LBound(SourceArray) To UBound(SourceArray, 1) ' How we have to proceed depends on whether we have seen the ' current MISDN in the last source row. Dim LastMISDN As String If LastMISDN SourceArray(SourceRow, 1) Then ' If we see the MISDN for the first time, we copy the ' MISDN into the first column and copy the service into ' first service column. TargetRow = TargetRow + 1 TargetArray(TargetRow, 0) = SourceArray(SourceRow, 1) TargetArray(TargetRow, 1) = SourceArray(SourceRow, 4) TargetColumn = 1 LastMISDN = SourceArray(SourceRow, 1) Else ' If we have seen the MISDN in the last iteration, we have to ' put the current service in the next column ' instead of the next row. TargetColumn = TargetColumn + 1 TargetArray(TargetRow, TargetColumn) = SourceArray(SourceRow, 4) End If ' Remember the MISDN that we have seen in this iteration and ' advance the source cell to the next row. SourceRow = SourceRow + 1 Next Worksheets(Desire format).Range(Cells(4, 1), _ Cells(4 + UBound(TargetArray), 11)).Value = TargetArray End Sub Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: VB Help needed.
On 12 Mai, 13:22, Born to Win cs4...@gmail.com wrote: Hi Experts, in attached excel file you can see contact no. along with many services activated on a single no. you can find it in separate entry i am trying to write a VB program so that i would have unique no. services (column wise) in new sheet in one column after a single click. please help me out Copy the following code into the worksheet Desire format, add a button that executes the sub PrintServiceColumnWise __ ' This forces us to declare all variables with a DIM statement (so ' we cannot get any errors due to mis-typed variable names). Option Explicit Sub PrintServiceColumnWise() Dim SourceCell As Excel.Range Set SourceCell = Worksheets(Base).Cells(2, 1) Dim TargetCell As Excel.Range Set TargetCell = Worksheets(Desire format).Cells(3, 1) ' Go through the first column in the source sheet until we find ' an empty cell. While SourceCell.Value ' How we have to proceed depends on whether we have seen the ' current MISDN in the last source row. Dim LastMISDN As String If LastMISDN SourceCell.Value Then ' If we see the MISDN for the first time, we copy the ' MISDN into the first column and copy the service into ' first service column. Set TargetCell = TargetCell.Offset(1, -TargetCell.Column + 1) TargetCell.Value = SourceCell.Value Set TargetCell = TargetCell.Offset(0, 1) TargetCell.Value = SourceCell.Offset(0, 3).Value Else ' If we have seen the MISDN in the last iteration, we have to ' put the current service in the next column ' instead of the next row. Set TargetCell = TargetCell.Offset(0, 1) TargetCell.Value = SourceCell.Offset(0, 3) End If ' Remember the MISDN that we have seen in this iteration and ' advance the source cell to the next row. LastMISDN = SourceCell.Value Set SourceCell = SourceCell.Offset(1, 0) Wend End Sub Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: error checking VBA Excel 2003
On Feb 15, BJthebear wrote: I have written a sub routine to add a name to a dynamic list but I have three problems with it and would be grateful for some advice. Sub InputNewUser() Dim Newuser As String Dim nextRow As Long [snipped some code] 'Ask for name of new user Newuser = Application.InputBox(Please enter name of new user (Surname first)) 'turns it into proper letters Newuser = Application.Proper(Newuser) 'Check to see if cancel button is pressed go back to Menu If Newuser = Then Sheets(Menu).Select Exit Sub End If 'Routine to check if Newuser already exists in list of users ' 'Find last row Set SrcSht = Sheets(UserList) nextRow = SrcSht.Cells(Cells.Rows.Count, A).End(xlUp).Row + 1 Application.ScreenUpdating = False Range(A nextRow).Select This line will probably cause problems. Note that the Range function is invoked on the ActiveWorksheet if you don't specify a worksheet. You should transform these lines in such a way that you get rid of any Active calls (those Activate calls are a giveaway that the original code was recorded by the macro recorder ;-) The replacement code should look like this: dim CurrentCell as Excel.Range set CurrentCell = SrcSht.Cells(1,1) ' Search the list of the new user name: This assumes that ' there are no empty rows in the list of users. While CurrentCell and CurrentCell Newuser Set CurrentCell = CurrentCell.Offset (1,0) Wend if CurrentCell = NewUser then Msgbox The user is already present in the list! exit sub end if ActiveCell.FormulaR1C1 = Newuser Columns(A:A).Select Selection.Sort Key1:=Range(A2), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Application.ScreenUpdating = True 'display message to show that it has been added to userlist MsgBox (Newuser has been added to List of Users) 'return to menu page Sheets(Menu).Select End Sub The problems are as follows:- 1. I have just added the code to check to see if the cancel button is pressed but it does not exit the subroutine - it adds a false to the list of users. 2. How do I check to see if the Newuser entry already exists in the list of users 3. The routine seems to work when running it from the VBA window but when the macro is attached to a button on the Menu Wrksheet it fails to add the name to the list of users. This routine worked up until a few days ago and it really has me stumped Any help would be appreciated Brian Scotland Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
$$Excel-Macros$$ Re: vba excel understanding objects : worksheet
On 23 Sep., cyber 1000s wrote:
Hi cyberspace,
I have some c programming background...
Is it possible to increment a worksheet object (worksheets collection)
in a loop such as below ?
Public Sub CopyShNamesFromWkbToWkb2()
Dim i As Integer
Dim wkb As Object
Set wkb = Workbooks(Nouveau_Feuille_Excel_1.xls)
Dim wkb2 As Object
Set wkb2 = Workbooks(Classeur1.xls)
Dim ws As Object
Set ws = Worksheets
For i = 1 To ws.Count
wkb.Sheets(3).Cells(i, 4) = wkb2.ws(i).Name'///A
Next i
End Sub
line ///A should be: wkb.Sheets(3).Cells(i, 4) =
wkb2.Sheets(i).Name
Why a property pointing on an object variable such as count is working
but not if it's being incremented like any excel vba name :
sheets(i)... and not ws affected as a sheet ?
Since you have experience with C, let's transpose your VBA question
into a C question:
/* The following definitions simulate the Excel run-time.
*/
typedef struct
{
char* Name;
} Worksheet;
typedef struct
{
Worksheet* Sheets;
} Workbook;
workbook* Workbooks; /* Global Workbooks collection */
Worksheet* Worksheets; /* Global Worksheets collection */
/* Here comes your code.
*/
void CopyShNamesFromWKbToWkb2 ()
{
int i;
Workbook* wkb; /* = Nouveau_Feuille_Excel_1.xls */
Workbook* wkb2;
Worksheet* ws;
ws = Worksheets;
for (i = 0; i Count(ws); ++i)
wkb[3].Cells[i,3] = wkb2.ws[i].Name
}
Can you spot your mistake now?
Regards,
Stuart
--
--
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wallref=ts
$$Excel-Macros$$ Re: fonts in combobox keeps becoming smaller as it is clicked
On 31 Aug., 03:27, Meimei xxu8810...@gmail.com wrote: I am writing a simple vba program. I used a few comboboxes in the program. But I found that as I click on the combobox, the font size in the drop-down list gets smaller each time, and after a few clicks, the font is too small to even see. Does anyone know why it happens? Thank you! This is apparently a bug in Excel that is hard to solve. I sometimes encounter this (it's usually a sign that I have to re-start Excel or that I should go home and spend some time with my children ;-). Seriously, you'll find a lot of such error descriptions on the net but nobody has a useful answer. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: I love excel because.......
On 28 Aug., Ayush wrote: Its a very handy tool for any data analysis and reporting. - Ayush Jain --- Guyzzz, Let me know why you like excel so muchThe best response will be published on the home page with thanks. Please continue in this thread only just below my line I want to see the active participation... Excel is a tool that brings together both the Engineer and the Software Developer: the first one can easily experiment with data, create charts, and develop algorithms. The second one can then write down those algorithms in a proper object-oriented manner. Furthermore, data is stored in a way that it can be accessed in nearly any programming language with the same ease that is attributed to XML. OTOH: I hate Excel because it crashes quite frequently, offers no auto- completion for variables, requires some external tools in order to be used with CVS/SVN. Also the compiler doesn't get all syntactical errors at compile time (there's no way to disable late binding for VBA). From a C++ perspective I hate that Excel doesn't support dual interfaces. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Sort excel columns with VBA
Kurt wrote: I am trying to duplicate the sort feature used in this tutorial: http://www.contextures.com/xlSort02.html The description of the first part of the code suggests that the code creates the invisible rectangles, but I couldn't get it to do this. What did you do? What kind of error message did you get. You'll have to be a bit more specific about your problem (which version of Excel, for example). (I had the create the rectangles manually, ahead of time.) You can demonstrate this using the sample Excel sheet provided (SortClick.zip). If you delete any of the hidden rectangles, save, close and then open the file, the code will not recreate the rectangle and the affected columns will not sort. That's right, you'll have to re-create the rectangles manually (or possibly put a call to the generator function in the Workbook_Open handler). To get around this, I tried to copy and paste a rectangle from the sample sheet to my own document. (And repasted it for the rest of my columns.) When I do this, it also copies the Macro assigned to that particular rectangle (SortClick.xls!SortTable). Things will work if I manually rename the macro assigned to each rectangle (using the name of my .xls file), but I was hoping to avoid that on the assumption the code can do it dynamically. Am I missing something? Thank you. Well, I just copied the macro code for both SetupOneTime and SortTable into a Code Module, run the SetupOneTime macro through Alt+F8, and everything worked as expected. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: automation error
On 19 Jul., mohd ateeq wrote: HI all, Can anyone help me out with the following error: Run-time error ‘-2147417848 (80010108)’: Automation error The object invoked has disconnected from its clients It occurs when I create around 30-40 new excel files based on some criteria and update as and when there is a updates by opening each excel file and update using the vba code. When this error occurs, there will be a one more file created with a junk name like 7EA78f10 ……and shows a save as dialogue box, when I click on cancel the above error message is displayed and when I save that file, it says “Excel has encountered a problem and need to close and it will start recovering the files again. Please help me out Thanks… On Jul 20, Stuart Redmann wrote: This error message indicates that something very fundamental is going wrong. Usually you will only get it if there is a COM server (for example Excel) that houses some COM objects (your Excel workbooks) shuts unexpectedly down (IOW, Excel crashes). Since it is quite easy to make Excel crash, it will be quite hard to find out what exactly causes the crash. The 7EA78f10 file you have mentioned may be a clue which of the opened Excel files caused the crash (this file is certainly the result from an auto-recover of Excel). However, we need _much_ more information about what you are doing, best of all a minimal example that exposes the behaviour. Do you create the new Excel files in a separate Excel server? If so, have you enabled macro processing in these servers? On Aug 2, mohd ateeq wrote: Actually i have a excel file in which there are names written of all the operators and each operator will have a excel file. In the macro code it will select a file usin the application.open method then it will calculate the data accordin to the criteria specified. After that it will open each excel file and update the data respectively. This error occurs at times while updatin the data. but no error is specified the only thing is it shows a save as dialogue box with the junk name i have specified before but when i click on cancel or save, the excel crashes. There is one thing that might work in this case: Instead of the line Application.Open you could create a new Excel server: Dim NewExcelApp As Excel.Application Set NewExcelApp = New Excel.Application NewExcelApp.AutomationSecurity = msoAutomationSecurityForceDisable Then you can open the workbook of the operators in the newly created application like this: dim OperatorWorkbook as Excel.Workbook set OperatorWorkbook = NewExcelApp.Open (FileNameForOperator.xls) ' Do whatever needs to be done for the operator. This worked for me when Excel used to crash in a reproducible fashion. In my case I tried to insert macro code into another worksheet (which works like a charm as long as macro processing is turned off for this worksheet). Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Re: Need to sum if data is in another cell
Hi Nadine, I've sent you an email with a file that hopefully does what you want. Please don't answer by email, since I have a quite selective spam- filter. Please post to this newsgroup instead. Thanks. Stuart Redmann wrote: I guess that what you wanted to say is that you want to get the sum of all payments that have their Check Date field set. If so, you can use the following formula for cell Amount Paid: =SUM(IF(Sheet1!ZS(1)0,Sheet1!ZS,0),IF(Sheet1!ZS(6)0,Sheet1!ZS(5), 0),IF(Sheet1!ZS(11)0,Sheet1!ZS(10),0),IF(Sheet1!ZS(16)0,Sheet1! ZS(15),0),IF(Sheet1!ZS(21)0,Sheet1!ZS(20),0)) Nadine S wrote: [snip] Unfortunately when I put Stuart's formula into cell C2 on sheet 2, I get #NAME? as the result and not $1950. I'm sorry, I did not properly translate everything from my German Excel into English, assuming that you have the English version (if you have any other version, I'll have to translate the formula into this language, which is a quite annoying feature of Excel ;-) The German ZS must be RC, and this will also only work if you turn on RC-referencing (so that you can reference cell A1 by R1C1). Stuart, first let me thank you for your email. In response to your comment of my few misspellings, let me apologize for my hurried email and therefore lack of running spell check. According to Brain Connection, proficient readers read most words (except very long ones) principally through the first and last letters and a rapid recognition of the general shape and content of the entire word. That's why we typically read misspelled words with no trouble. My misspellings didn't seem so outrageous to me but maybe that's just me. I think that leaving misspellings uncorrected is established behaviour for chatting, but inacceptable for newsgroups and email . I know that this group is a bit different from others (if you posted Plz hlp me ASAP. URGENT to comp.lang.c++, you'll most probably not get any answer). In regards to, How many columns arel you going to insert into the worksheet? I'm assuming that your word arel is supposed to be are. With that assumption, the answer is I don't know. As many as needed would have to be my best guess at this point. Currently there are 21 but that may need to increase in time or possibly even decrease. Basically I just don't know. [snip] Your assumption that this is to handle lots of rows is correct, however, I don't understand what you mean when you said, (which probably stand for some client). Well, I assumed that the payments in a row belong to the same person or company (that's why I used the more abstract term client here). I further assumed that there is more than one client, so you'll have lots of rows, each row for a different client. If this is so, you'll get into trouble if there are lots of clients _and_ lots of payments per client. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Need to sum if data is in another cell
Nadine S wrote: I have a worosheet with cokumns for 20+ payments. Please, if you want to get help from others, make sure that your posting is not full of typos. It makes reading quite hard. These columns track the date it was entered for payment, amount, check date, record number for each of the 20+ columns. Just out of curiousity: How many columns arel you going to insert into the worksheet? Is there any limit? You should note that Excel 2003 and older can only cope with 255 columns. In another sheet, I want to sum the amount entered and the amount paid by line. Meaning, row 2 on sheet 1 ties to row 2 on sheet 2 so I don't need to do lookups or sumif type. At least I don't think so. :) So, I have a formula that sums all the amounts entered but now I need one to sum the amounts is there is a check date and record number. I don't quite get what you want to achieve (sorry, but your English is not so good). I guess that what you wanted to say is that you want to get the sum of all payments that have their Check Date field set. If so, you can use the following formula for cell Amount Paid: =SUM(IF(Sheet1!ZS(1)0,Sheet1!ZS,0),IF(Sheet1!ZS(6)0,Sheet1!ZS(5), 0),IF(Sheet1!ZS(11)0,Sheet1!ZS(10),0),IF(Sheet1!ZS(16)0,Sheet1! ZS(15),0),IF(Sheet1!ZS(21)0,Sheet1!ZS(20),0)) Note that this works only for the currently defined columns. If you add more columns, you'll have to edit formulas for Amount Entered and Amount Paid. As there is a limit to the length of a formula, you may get in trouble even before you run out of columns. I've attached a file as a sample that I think will help you to understand. Thanks for everyone's help. It looks to me as if your workbook should be able to handle lots of rows (which probably stand for some client) and lots of payments. In that case you'll get sooner of later into trouble if you want to get all data into a single sheet. You can get around this if you (a) use Excel 2010, or (b) use a simple database, for example Access. The later has the benefit that more than one user can enter data. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Macro for tracing the changes
sudhir kumar wrote: hi friend, i attached an excel file, which having some information in it, now i want that if i will do any type of change in it, i will be able to trace it, means, i change in some of the column or row, or i introduce any new row ,or delete any row. every type of change i want to trace., Why don't you just turn on the macro recorder? This component pretty much saves a playback of all the actions the user does. The recorded macro can be used to generate a log file. I can't think of any other way. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: equation editor in excel 2010
Santiago Afonso wrote: Hello. I'd like to use the equation editor in excel 2010 through VBA. In Word, the equation object is OMath ( http://msdn.microsoft.com/en-us/library/bb256563%28office.12%29.aspx), and the macro recorder records some actions done to such an object (like changing equations from professional to inline). Excel 2010's macro recorder does not reflect interactions with any object other than shapes when using the equation editor, and I found no information regarding the usage of the OMath object under Excel. It seems to me as if the OMath object is just Word's wrapper for mathematic formulas, the actual COM object is Microsoft Equation X. 0. This object is provided by Design Science Inc., and as far as I can see on their homepage they don't have an Automation interface for their component (they rather advertise the feature that one can import MathML into their component, but you'll have to buy the latest version of their software). Due to the nature of COM, the macro recorder cannot possibly track any user interactions that are done to embedded objects. This means that you're out of luck. A quick scan with Google yielded very little usable information: It seems that automatable formula editing controls are quite rare. Most probably it will be the easiest if you use Latex to typeset your formulas and import the generated graphics into Excel. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Remove selected cell contents
flebber wrote: I have a column containing cells which house several delimited values and a dollar value. I want to be able to remove the delimited values and leave the dollar value in the cell in currency format. the cells contain information similar to 22-7-4-7 $22400.10 If this text is in cell A1, you can enter the following formula into some other cell and retrieve the dollar value from A1. Then you can format the new cell as currency manually (you can do this for lots of cells in one go). =RIGHT(A1,LEN(A1) - FIND($,A1)) Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Reverse of rounding
On 20 Jul., larry wrote: I have a macro where I copy data into an array from one sheet and then post it in another sheet and there is something odd. .8 becomes , 79, .15 becomes .1501. The other way around this would be rounding. It only changes the values in the first column. Any ideas on what is going on? On 21 Jul., larry wrote: Since the data was hard entered, typed directly, there shouldn't be any extra digits hanging around. It does not matter whether you have entered the number exactly because Excel has to transform your text into a floating point number. Since there is no floating point number that has the value 0.15, Excel will use a floating point number that is closest to 0.15 (in your example 0.151). However, Excel performs some magic with floating point values so that it will display 0.15 even if the floating point value is 0.151 (see http://www.cs.berkeley.edu/~wkahan/ARITH_17.pdf for some examples of what Excel does). Apparently Excel does not cope with the case that the number is entered via some macro, hence you see the slighly off values. This may be a bug, although some people rather consider Excel's displaying 0.15 a bug. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: automation error
On 19 Jul., mohd ateeq wrote: HI all, Can anyone help me out with the following error: Run-time error ‘-2147417848 (80010108)’: Automation error The object invoked has disconnected from its clients It occurs when I create around 30-40 new excel files based on some criteria and update as and when there is a updates by opening each excel file and update using the vba code. When this error occurs, there will be a one more file created with a junk name like 7EA78f10 ……and shows a save as dialogue box, when I click on cancel the above error message is displayed and when I save that file, it says “Excel has encountered a problem and need to close and it will start recovering the files again. Please help me out Thanks… This error message indicates that something very fundamental is going wrong. Usually you will only get it if there is a COM server (for example Excel) that houses some COM objects (your Excel workbooks) shuts unexpectedly down (IOW, Excel crashes). Since it is quite easy to make Excel crash, it will be quite hard to find out what exactly causes the crash. The 7EA78f10 file you have mentioned may be a clue which of the opened Excel files caused the crash (this file is certainly the result from an auto-recover of Excel). However, we need _much_ more information about what you are doing, best of all a minimal example that exposes the behaviour. Do you create the new Excel files in a separate Excel server? If so, have you enabled macro processing in these servers? Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Reverse of rounding
On 20 Jul., 14:26, larry wrote: I have a macro where I copy data into an array from one sheet and then post it in another sheet and there is something odd. .8 becomes , 79, .15 becomes .1501. The other way around this would be rounding. It only changes the values in the first column. Any ideas on what is going on? Have you checked whether the receiving cell has set the same number of digits after the comma? Maybe the superfluous information is just not displayed in the source cell. BTW, the floating point arithmetic imposes the restriction on us that decimal fractions that are no binary fractions cannot be stored as exact values. So what you see is most probably not some kind of inverse rounding but just the exact value of the floating point variable. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Excel Programming
On 5 Jul., dtenz wrote: I have name cells A1 to D1 as Number Num1 Num2 and Num3. What? The cells A1 to D1 contain _four_ values. How do they correspond to Num1, Num2, and Num3? Please be as precise as possible. Now I have data in Col A from A1 to around A500 depending on the file. What I need to do is: If Number is =25, then Num1=25 and If Number is 25, then Num1=Number. What is Number? Where does it come from? What you are trying to do can be achieved by the max function: =max(A1;25) should do the trick For Num2: if Number-Num1=25 then Num2 = 25 else Num2=Number-Num1 For Num3: Num3=Number-Num1-Num2 The cell I am trying to calculate will be B2 and rest of col B = Num1, C2 and rest of col C = Num2 and D2 and rest of col D = Num3. That's not very clear to me. Before you post the next time, please show your description to someone else. If they understand it without having to ask you _any_ questions, you can post it to this newsgroup. Thank You, dtenz Please don't get me wrong, we don't want to harras you. You have to understand that we posting to this group in our free time. We are happy to help you with your problem, but we don't like to spend much time to find our what your problem actually is. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Macro to shade every other row
On 30 Jun. Kurt wrote: 1. The comment in this code has as an example, ShadeAlternateRows Range(A1:D50), 27, 2, but where in the actual code do you put this information? Stuart Redmann wrote: That depends on when the highlighting should happen. Kurt wrote: I'm most interested in learning how to do it manually (e.g., after manually selecting a range). Ok. First of all, you can only use subs that take no arguments (and then it does not matter where you put it). The macro must be re- designed to use the current selection (also we have to fix the step width and the color): Public Sub ShadeAlternateRows() With Selection ' remove any previous shading .Interior.ColorIndex = xlColorIndexNone ' Shade every second row in yellow (= color index 27) Dim r As Long For r = 2 To .Rows.Count Step 2 .Rows(r).Interior.ColorIndex = 27 Next r End With End Sub Now you this sub appears in the macro list and can be assigned a shortcut. If you need to be able to use different colors, you'll have to pop up a dialog that lets the user choose a color. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Macro to shade every other row
On 29 Jun., Kurt wrote: I would like to highlight every other row in a range (A5:L40). I've seen lots of macro samples to shade alternate rows, but can't get any to work. For example, I found this one: Sub ShadeAlternateRows(rngTarget As Range, intColor As Integer, lngStep As Long) [snip] 1. The comment in this code has as an example, ShadeAlternateRows Range(A1:D50), 27, 2, but where in the actual code do you put this information? That depends on when the highlighting should happen. You provided the answer to this question yourself, see question three. 2. When I put this code in the VB of my worksheet, it is not listed among the macros when I go to Tools Macro Macros. Any idea why? You have to make the code a public sub. Just put the keyword public in front of sub. 3. If I want to run this macro, for example, on Worksheet ABC every time the workbook is open, I understand I need to put the code in the Workbook_Open module (between Private Sub Workbook_Open() End Sub). If I do that, how do I modify the code (which already has a Sub and End Sub line.) You have to add the line ShadeAlternateRows Range(A1:D50), 27, 2 in the Workbook_open method, not the code of ShadeAlternateRows. ShadeAlternateRows is best be placed in the code module that is called ThisWorkbook (I'm not sure about the name since I use a different locale). Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: abstract class / interface in VBA
On 10 Jun.schouw wrote: How do I define and implement an interface in VBA? I would like to keep the implementation flexible but the interface should stay stable. See http://msdn.microsoft.com/en-us/library/aa165323(v=office.10).aspx. I've never checked out this feature of VBA, but it will most certainly be handy. What MSDN does not tell you is how to declare interfaces. I tested this and came up with the following insights: Your interface module is a plain old class module that contains properties and procedures but with the following exceptions: - the code in the procedures (subs and functions) will never be used if you use the module only as an interface. - the variables (properties in the parlor of VBA) must be implemented in the implementation module by supplying setter and getter functions. Providing an implementation is done through the Implements keyword (you can even implement multiple interfaces in one module). Note that the implementation of an interface in an all-or-nothing thing: You'll have to implement all procedures of the interface. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Form Control Checkboxes
On 10 Jun., 14:14, Rod Urand urand...@gmail.com wrote: Stuart, Thank you for going to all the trouble to build this. I do have a problem. I just found out that they are indeed free floating checkboxes that are not attached to cells. Are we just out of luck here? Not at all, I was just using the attached cell for the cell link because that seemed to be the most logical solution to me. If the checkbox is free floating you can use the the very same code, you just have to remove the line Debug.Assert cb.Placement xlFreeFloating from the code I have posted already (and probably also the comment that preceeds this line). Anyway, if you run the code without any changes, Excel will pop up the code editor and mark the currently executed line with a yellow background color. You can simply hit F5 repeatedly (as often as there are free floating checkboxes in your sheet) until the code has finished since the Debug.Assert instruction just tells Excel that something is not as it is supposed to be (in our case we can safely ignore this warning). You can also simply change the code in such a way that the linked cells are at a certain position in the sheet, for exampe in the first column (if the checkboxes are spread all over the worksheet, you'll still have some trouble with tabulating the number of clicked boxes, I guess). I leave it as a kind of exercise for you to figure out how to do this (hint: The line starting with cb.LinkedCell = is where you have to make your changes). If you need any help, feel free to ask (I'll be offline for the weekend, though). Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Form Control Checkboxes
On 9 Jun., 16:15, Rod Urand urand...@gmail.com wrote: I have a client who has created a rather large Excel workbook with checkbox form controls. Unfortunately, each form control was not set to have a cell link. The client would like to now tabulate the results of the checkboxes (to determine how many are checked and how many are unchecked). Rather than go into each cell and modify the form control property to then add a cell link, I am looking to automate this process through the use of a macro. Essentially speaking, the macro will locate each checkbox in a workbook, modify the control properties, add a cell link to the cell adjacent to the checkbox. This should get you started (copy it into the module of the worksheet you want to convert and call it as macro). Option Explicit ' Links all checkboxes of the current workbook with ' the cells they belong to (assuming that the checkbox ' is not a free floating checkbox). Sub CreateCellLinks() Dim i As Integer For i = 1 To Me.Shapes.Count ' Skip any shapes that are no checkboxes. If TypeName(Me.Shapes(i).DrawingObject) = CheckBox Then Dim cb As CheckBox Set cb = Me.Shapes(i).DrawingObject ' The following only works if the checkbox is not a floating ' checkbox (it must be placed inside a cell). Debug.Assert cb.Placement xlFreeFloating cb.LinkedCell = ColumnLetter(cb.TopLeftCell.Column) cb.TopLeftCell.Row End If Next End Sub ' Downloaded from http://www.freevbcode.com/ShowCode.asp?ID=9264 Function ColumnLetter(ColumnNumber As Integer) As String ' 'example usage: ' 'Dim temp As Integer 'temp = Sheets(1).Range(B2).End(xlToRight).Column 'MsgBox The last column of this region is _ 'ColumnLetter(temp) ' If ColumnNumber = 0 Then 'negative column number ColumnLetter = ElseIf ColumnNumber 16384 Then 'column not supported (too big) in Excel 2007 ColumnLetter = ElseIf ColumnNumber 702 Then ' triple letter columns ColumnLetter = _ Chr((Int((ColumnNumber - 1 - 26 - 676) / 676)) Mod 676 + 65) _ Chr((Int((ColumnNumber - 1 - 26) / 26) Mod 26) + 65) _ Chr(((ColumnNumber - 1) Mod 26) + 65) ElseIf ColumnNumber 26 Then ' double letter columns ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) _ Chr(((ColumnNumber - 1) Mod 26) + 65) Else ' single letter columns ColumnLetter = Chr(ColumnNumber + 64) End If End Function A sample worksheet is available but I do not know how to attach it. Me neither :-) Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Excel Macro for Dos Prompt
On 6 Jun., 13:16, kamal shah kamal...@gmail.com wrote: Hi Group, I am looking for a Macro, which can execute commands on DOS Prompt. My actual requirement is, to pick up each cell value (which is apparently a DOS command ) and execute them on command prompt. Macro needs to do following, 1. pick up value from Cell 2. Open Command prompt ( start -- cmd) 3. Change Directory - (cd c:\temp) 4. Paste VALUE picked from line 1 Your help on this will be highly appreciated. I tried to send you an Excel workbook as attachment, but I couldn't figure out how to do so (I'm using Google Groups :-( The following is the code in plain text. You'll have to copy it into a new worksheet. Then you can enter dos commands into the cells A4, B4 and C4, such as cmd /c copy C:\Temp\foo.txt C:\Temp\bar.txt cmd /c dir notepad Regards, Stuart Code Module for Table 1: Option Explicit Private Sub ExecuteDOSCommands_Click() Dim i As Excel.Range For Each i In Range(A4:C4) Cells(i.Row + 1, i.Column) = ExecuteApp(i.Value, INFINITE, True, True) Next End Sub -- Code Module Kernel32 - Public Declare Function CreatePipe Lib kernel32 ( _ phReadPipe As Long, _ phWritePipe As Long, _ lpPipeAttributes As Any, _ ByVal nSize As Long) As Long Public Declare Function PeekNamedPipe Lib kernel32 ( _ ByVal hNamedPipe As Long, _ lpBuffer As Any, _ ByVal nBufferSize As Long, _ ByVal lpBytesRead As Long, _ lpTotalBytesAvail As Long, _ ByVal lpBytesLeftThisMessage As Long) As Long Public Declare Function ReadFile Lib kernel32 ( _ ByVal hFile As Long, _ ByVal lpBuffer As String, _ ByVal nNumberOfBytesToRead As Long, _ lpNumberOfBytesRead As Long, _ ByVal lpOverlapped As Any) As Long Public Type SECURITY_ATTRIBUTES nLength As Long lpSecurityDescriptor As Long bInheritHandle As Long End Type Public Type STARTUPINFO cb As Long lpReserved As Long lpDesktop As Long lpTitle As Long dwX As Long dwY As Long dwXSize As Long dwYSize As Long dwXCountChars As Long dwYCountChars As Long dwFillAttribute As Long dwFlags As Long wShowWindow As Integer cbReserved2 As Integer lpReserved2 As Long hStdInput As Long hStdOutput As Long hStdError As Long End Type Public Type PROCESS_INFORMATION hProcess As Long hThread As Long dwProcessId As Long dwThreadID As Long End Type ' Konstanten für CreateProcess. Public Const NORMAL_PRIORITY_CLASS = H20 Public Const STARTF_USESTDHANDLES = H100 Public Const STARTF_USESHOWWINDOW = H1 Public Const SW_HIDE = 0 Public Const SW_SHOW = 5 Public Declare Function CreateProcessA Lib kernel32 (ByVal _ lpApplicationName As Long, ByVal lpCommandLine As String, _ lpProcessAttributes As Any, lpThreadAttributes As Any, _ ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _ ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _ lpStartupInfo As Any, lpProcessInformation As Any) As Long Public Declare Function CloseHandle Lib kernel32 (ByVal hObject As Long) As Long Public Declare Function GetTickCount Lib kernel32.dll () As Long ' Konstanten für WaitForSingleObject Public Const INFINITE = -1 ' Im Netz findet man immer mal wieder H, was von Excel intern zu -1 umgewandelt wird. Public Const SYNCHRONIZE = H10 Public Const WAIT_FAILED = -1 Public Const WAIT_TIMEOUT = H102 Public Declare Function WaitForSingleObject Lib kernel32 (ByVal hHandle As Long, _ ByVal dwMilliseconds As Long) As Long Public Declare Sub Sleep Lib kernel32 (ByVal dwMilliseconds As Long) ' Declarations for FormatMessage Public Const FORMAT_MESSAGE_FROM_SYSTEMAs Long = H1000 Public Const FORMAT_MESSAGE_IGNORE_INSERTS As Long = H200 Public Const FORMAT_MESSAGE_MAX_WIDTH_MASK As Long = HFF Public Const LANG_USER_DEFAULT As Long = H400 Public Declare Function FormatMessage _ Lib kernel32 Alias FormatMessageA ( _ ByVal dwFlags As Long, _ ByRef lpSource As Any, _ ByVal dwMessageId As Long, _ ByVal dwLanguageId As Long, _ ByVal lpBuffer As String, _ ByVal nSize As Long, _ ByRef Arguments As Long _ ) As Long Public Const ERROR_BROKEN_PIPE As Long = 109 -- Code Module ShellHelper - ' Downloaded from http://www.bigresource.com/Tracker/Track-vb-XYs37h2gvm/ and modified ' ' History: ' 2010-06-07: Stuart Redmann ' - Added time-out for reading from the pipe. ' - Added parameter
$$Excel-Macros$$ Re: Running Macros when Multiple Excels are open
On 1 Jun., 01:57, donvreug donvr...@gmail.com wrote: The environment this is occuring in is a corporate one where there are a number of Excel workbooks each with VBA modules that are deisgned for different tasks. Occassionally someone tells me a macro has failed, and when I check they usually have two or more Excel workbooks open. Who is they? Usually one knows which workbooks one has opened and particularly why. Or is this some kind of environment thing, such as that is just some stuff that appears automatically when I boot my machine. What are the tasks that these workbooks are designed for? Although an Excel workbook normally just sits there and does pretty much nothing, one can design workbooks that regularly do some system maintenance jobs like back-ups. That would make debugging quite hard (you would first have to determine which Excel application is giving the error message). Or do you mean that when you perform some action you get occassionally an error message (in contrast to the scenario where you do something else and suddenly an error message pops up)? And what is the exact text of the error message? Please elaborate. Closing all workbooks and then reopening just the one which failed usually lets it run successfully. So there must be some kind of interaction at the macro level between these workbooks. It could be very well be that different worksheets are trying to operate on the same set of files. This would lead to Access Denied errors. Some of the modules occur in more than one Excel workbook and there may be common sheet names as well. I was wondering if there is a way to isolate the macro, in either code or some other way, so it only interacts with its parent workbook or whether this should be happening anyway? I don't think that it is possible to restrict Excel's macro processor in such a way that macros can only manipulate the worksheet that they belong to (I'm not an expert in these matters, maybe Office 2007 and later offer such functionality). As far as I know, any macro can manipulate any workbook (and even the code in other workbooks, see my previous answer). Perhaps a better way of implmenting workbooks in such an environment is required? VSTO perhaps? I can imagine that VSTO should make anything better with regard to your problem. I just guess that you'll have to find out what is going wrong by yourself (which is usually the hardest part of programming). Good luck, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Running Macros when Multiple Excels are open
On 30 Mai, 02:26, donvreug donvr...@gmail.com wrote: Some of the macros I have written sometimes do not function correctly if another instance of Excel is open. The other instance usually also contains macros that may or may not have been executed. I thought I read somewhere that this is a common problem but a recent search has not turned up anything. Does anyone know of a solution to this or any tips to minimise the chances of this happening? I am using both Excel 2003 and 2007. Thanks. I encountered quite the reverse: I use a macro that creates another workbook and adds code to it. If I let this newly created workbook run in the same instance of Excel, it reliably crashes (Excel 2003). The solution for me was to launch a separate instance of Excel, disable the macro processing inside this instance and create the workbook there (the performance overhead of using the other OutProc Excel instance is negligible in my case). To say something about your problem is quite hard since you have provided very little background information. Are the other instances of Excel in any way related to your instance? Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Re: Excel Group Survey : Please participate.
On 30 Mai, 17:55, ayush jain jainayus...@gmail.com wrote: Dear Group, Thanks for all your contribution to group. The group recently achieved a milestone of 7000 members. On this occasion, we would like to take your feedback and suggestions about the group. This is an anonymous survey , so feel free to share your views wherever applicable. Your valuable feedback is highly appreciated. To participate in survey, Please follow the below link http://www.surveymonkey.com/s/L8BTDNQ Thanks again and Keep Posting. :) Best regards, Ayush Jain Group Manager Dear Mr. Jain, if I don't regularly visit any group that is mentioned in question 8, I still have to select one of them in order to be able to finish the survey. That seems like a bug to me. Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 7000 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Is it possible that a sub procedure will do certain steps and at the end it will delete itself
On 18 Mai, 16:10, Hiren Sheth hiren.sh...@viteos.com wrote: Hi, Is it possible that a sub procedure will do certain steps and at the end it will delete itself. Seems to be a strange question but is it possible? It certainly seems so (see for example http://www.vbaexpress.com/kb/getarticle.php?kb_id=250). However, I encountered some problems with Excel 2003 when I tried to add code to a workbook that was opened in the same Excel application (Excel sometimes crashed with an Access Violation). This malfunction leads me to believe that code that deletes itself is very likely to crash Excel as well. To put it like some sportswear manufacturer: Just try it! Regards, Stuart PS: If you post to newsgroups, you can safely turn off your privacy disclaimer (after all, your posting is _supposed_ to be readable by everyone :-) -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
$$Excel-Macros$$ Problem with array properties
Hello newsgroup, I have a simple Class module called TestClass that defines an array property: TestClass.cls: Private InternalArray(0) As Double Public Property Get MyArray() As Double() MyArray = InternalArray End Property Public Property Let MyArray(NewValue() As Double) InternalArray(0) = NewValue(0) End Property In some other module I have the following code: Function FunctionReturningArray() As Double() Dim Retval(0) As Double Retval(0) = 42# FunctionReturningArray = Retval End Function Sub test() Dim a As New TestClass a.MyArray = FunctionReturningArray ' WORKS! Dim b(0) As Double b = FunctionReturningArray ' Gives error: Cannot assign arrays. ' That is quite expected. a.MyArray = b ' Gives error: Cannot assign arrays. WHAT??? End Sub Can anybody explain why it is impossible to access the property MyArray directly? Thanks in advance, Stuart PS: I'm using VB6.0 inside Excel 2003 (don't know about any service packs). -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
Re: $$Excel-Macros$$ Validating multiple sheets with VBA
On 5 Mai, 11:18, Andy andyr...@hotmail.co.uk wrote: Heya, It's as simple as the subject states but I can't seem to find an answer anywhere. My code at the moment is: Worksheets(Array(Sat EEA, Sun EEA, Mon EEA, Tue EEA, Wed EEA, Thu EEA, _ Fri EEA, Sat NonEEA, Sun NonEEA, Mon NonEEA, Tue NonEEA, Wed NonEEA, _ Thu NonEEA, Fri NonEEA)).Select 'Column C Validation With Range(C9).Validation .Delete [snipped code that sets validation for some cells] 'Continues for ~20 other cells. End sub Validation doesn't appear to work when sheets are selected as an array and I'd rather not copy and paste the 100+ lines of code for each sheet in the workbook. I cannot use For Each Worksheet as a few sheets do not need the validation. Why don't you just put the code that sets the validation stuff into a sub? For example sub SetValidation (p_Worksheet as Excel.Worksheet) 'Column C Validation With p_Worksheet.Range(C9).Validation .Delete : end sub Then you can invoke it by: SetValidation Worksheets (Sat EEA) SetValidation Worksheets (Sun EEA) : If you really must use an array of worksheet names, you could also write: Dim it as Variant for each it in Array(Sat EEA, Sun EEA, ...) SetValidation Worksheets (it) next Regards, Stuart -- -- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe
