add many command button

[Mr. Zancky]

Hi,

I wrote the code to import text files (composed by 2 columns: x and y)
in excel sheet without using the Import Data function. For every file
imported I added a command button (locked with relative columns) adding
this line code:

*ActiveSheet.OLEObjects.Add "Forms.CommandButton.1", Left:=0, Top:=0,
Width:=109.5, Height:=24 *
*
In this way I created as many command button as set of x-y data.
Furthermore I created a global variable that increase by 1 every time I
imported a text file.
I have to question that I cannot solve:
1. how can I control every command button created? There's an index
that mark everyone?
2. the most important question: when the * line is added the global
variable doesn't increase, whereas when the line is not present all
works fine. I don't understand why!

Can anyone halp me?
Thank you in advance

 

[Leith Ross]

Hello Mr. Zancky,

You are correct that each command button has its own index. If these
Command Buttons are the only OLEObjects on the worksheet, they will
start at one and increment by one each time another is added to the
collection. You can use Activesheet.OLEObjects.Count to return the
number of OLE Objects on the worksheet. As for you question about the *
line problem, I am not sure what you are doing. Could you provide a code
example and explain what you want it do?

Thanks,
Leith Ross

 

[Mr. Zancky]

Hi Leith Ross,
thank you for your help.

This is part of the code:

************************************************************

Public appPath As String
Public nomeFile As String
Public varIndex As Intege
____________________________________________________________

Public Sub Data_Import(ByVal appPath As String, ByVal nomeFile A
String)

Dim NextCell As Range
Dim StartAddress As String
Dim EndAddress As String
Dim ComButtNumber As String


varIndex = varIndex + 1 'the variable is set to 0 in the initia
part of the code
Debug.Print varIndex

'Import data from *.txt file
With ActiveSheet.QueryTables.Add(Connection:= _
appPath, _
Destination:=Range("A4"))
.Name = "tab_dati_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

'formatting "Time" and "Kinetics" cell
Range("A6:B6").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Font.Bold = True
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
.Interior.ColorIndex = 15
.Interior.Pattern = xlSolid
End With

[...]

Foglio1.OLEObjects.Add "Forms.CommandButton.1", Left:=0, Top:=0
Width:=109.5, Height:=24


End Sub

***************************************************************

In the Excel worksheet I added a customized menu that allow me t
import the text file and avoid me all those steps with the 'Impor
Data' command. All things works well without the OLEObjects line cod
and the data are correctly imported and formatted.

But since I have to import many text file, i.e. many data that could b
plotted, I would like to add a command button to each set of data i
order to let me choose if plot the data or not. And at this point come
the problem: The code line is right because it creates the comman
button every time I imported the txt file but I have to put that lin
at the end of the funcion otherwise the button is not shown in th
worksheet.
But what I'm not able to understand is the global variable varIndex: i
has to increase by 1 every time I imported the txt file and without th
OLEObjects line all works well. As I add that line it seems that th
variable is a local variable but I defined it global!

Any suggestion

 

[Peter T]

I assume you are running the code in the same workbook to which you are
adding new Worksheet controls. If so, I suspect your problem is due to the
code recompiling immediately after and destroying all your public variables.

Ideally run the code from another workbook or addin. As a workaround perhaps
you could write and increment your variables to cells somewhere.

Depending on what else you intend to do at the same time, eg adding to a
collection of withevents class's, your code and Excel could crash
(particularly if on active sheet).

How do you intend to add event code to your new ActiveX buttons. Have you
considered Forms buttons, give each a unique name and assign a macro to the
OnAction property. In the macro use Application.Caller to identify the
button and from that what action to take.

Regards,
Peter T

Hi Leith Ross,
thank you for your help.

This is part of the code:

************************************************************

Public appPath As String
Public nomeFile As String
Public varIndex As Integer
____________________________________________________________

Public Sub Data_Import(ByVal appPath As String, ByVal nomeFile As
String)

Dim NextCell As Range
Dim StartAddress As String
Dim EndAddress As String
Dim ComButtNumber As String


varIndex = varIndex + 1 'the variable is set to 0 in the initial
part of the code
Debug.Print varIndex

'Import data from *.txt file
With ActiveSheet.QueryTables.Add(Connection:= _
appPath, _
Destination:=Range("A4"))
Name = "tab_dati_1"
FieldNames = True
RowNumbers = False
FillAdjacentFormulas = False
PreserveFormatting = True
RefreshOnFileOpen = False
RefreshStyle = xlInsertDeleteCells
SavePassword = False
SaveData = True
AdjustColumnWidth = False
RefreshPeriod = 0
TextFilePromptOnRefresh = False
TextFilePlatform = 850
TextFileStartRow = 1
TextFileParseType = xlDelimited
TextFileTextQualifier = xlTextQualifierDoubleQuote
TextFileConsecutiveDelimiter = True
TextFileTabDelimiter = True
TextFileSemicolonDelimiter = False
TextFileCommaDelimiter = False
TextFileSpaceDelimiter = False
TextFileColumnDataTypes = Array(1, 1)
TextFileTrailingMinusNumbers = True
Refresh BackgroundQuery:=False
End With

'formatting "Time" and "Kinetics" cell
Range("A6:B6").Select
With Selection
HorizontalAlignment = xlCenter
VerticalAlignment = xlBottom
Font.Bold = True
WrapText = False
Orientation = 0
AddIndent = False
IndentLevel = 0
ShrinkToFit = False
ReadingOrder = xlContext
MergeCells = False
Interior.ColorIndex = 15
Interior.Pattern = xlSolid
End With

[...]

Foglio1.OLEObjects.Add "Forms.CommandButton.1", Left:=0, Top:=0,
Width:=109.5, Height:=24


End Sub

***************************************************************

In the Excel worksheet I added a customized menu that allow me to
import the text file and avoid me all those steps with the 'Import
Data' command. All things works well without the OLEObjects line code
and the data are correctly imported and formatted.

But since I have to import many text file, i.e. many data that could be
plotted, I would like to add a command button to each set of data in
order to let me choose if plot the data or not. And at this point comes
the problem: The code line is right because it creates the command
button every time I imported the txt file but I have to put that line
at the end of the funcion otherwise the button is not shown in the
worksheet.
But what I'm not able to understand is the global variable varIndex: it
has to increase by 1 every time I imported the txt file and without the
OLEObjects line all works well. As I add that line it seems that tha
variable is a local variable but I defined it global!

Any suggestion?

 

[Peter T]

PS, see Stephen Bullen's comments
http://tinyurl.com/d38pg

Regards,
Peter T

 

[Mr. Zancky]

Thank you very much Peter,
you are right.
The way was not the right one for my purpose. In fact I was thinkin
how to add the code to the command button after created it when th
program execute...
I replaced them with a listbox with new item (dat set) added whe
imported and let me choose wich to plot.
I hope this is a better solution.
Thanks a lo