A few questions, if somebody could please assist -i've been doing my head in to get my vba project c

C

Clinton M James

Almost finished my vba application (YAY!) just some things remainf
outstanding if anybody could please help:

I have a spreadsheet i use as a log for requests which si separate to the
spreadsheet the userform i have made runs from. It loads up this log and
logs requests from people and then later uses this log to go through and
process the requests.

What I would like to do is have a userform that a user can click on to view
the status of their request and this will be based on the contents of the
log although i wish to change the values in the log to a mroe user friendly
vocab... I want to use a listbox and i know you can set the columns, but how
do you make each column variable in width and to the length of the biggest
text such as autofit does with cells?

Also, how do i specify for the data to go into the column?

I have tried userform1.listbox.column(1).additem but obviously this is not
right for it failed.


ok question 2.....

I have it set up that it detects the userid of the person logged in and
compares it to a lit of ids who have admin access.. if they have admin, then
they get extra buttons - one button is to allow them to manually edit the
log file - so as if a mistake was made or something failed previously and it
needs to be re-done on a future process run.

The userform being open stops me loading up the logfile for editing and I do
not know of a way to load the entire worksheet into a userform for edit...
so can somebody assist in directing me on how i can do either option?

Lastly,

I have had no success with finding out how to put telnet into the code...
all i needed to do was have it telnet to a location, log in, run a command,
then exit... I know the command prompt telnet doesn't allow script files
like the ftp does and any telnt code i have seen has lost me as my level of
expertise is limited... anyway is there a simple to understand telnet code i
can put in ... if not, is there a dos run telnet i can use that allows
scripts .. that way i can have vba write the script and bring up a command
prompt to execute the script... i hate sendkeys as it sometimes doesnt send
anything, besides you cant touch the computer when it's sending.

On the same theme... is there a command prompt based untar program that can
be scripted as well? that way i can fully automate the task rather than have
my program ftp a script through a script, quit, load up telnet through
sendkeys, logout, go back to ftp, then untar a file, then sort the
components into relevant directories for collection.

it;d be nice to have it all automated and not all but the untarring part,
which more or less is where I am at now.

If there are no dos based programs are there any in windows that can be
executed via vba and use a script?

My limitation is due to system setup, I am incapable of installing any
software, therefore it will need to be a self-executing file with no
install.

I've been doing my head in trying to get this to work and i've taken too
long as it is on the whole project.

Help is greatly appreciated and thank you in advance.
 
T

Tom Ogilvy

This sets up a multicolumn listbox using the widths of the columns on the
worksheet.
Private Sub UserForm_Initialize()
Dim sh As Worksheet, i As Long, s As String
Dim a As String, b As String
Dim c As String, wdth As Double
Me.ListBox1.ColumnCount = 3
Set sh = Worksheets("Sheet1")
sh.Range("A:C").EntireColumn.AutoFit
wdth = 1
For i = 1 To 3
wdth = wdth + sh.Columns(i).Width
s = s & sh.Columns(i).Width & ";"
Next
s = Left(s, Len(s) - 1)
Me.ListBox1.Width = wdth
Me.ListBox1.ColumnWidths = s
For i = 1 To 10
a = sh.Cells(i, 1).Text
b = sh.Cells(i, 2).Text
c = sh.Cells(i, 3).Text
With Me.ListBox1
.AddItem a
.List(.ListCount - 1, 1) = b
.List(.ListCount - 1, 2) = c
End With
Next
End Sub

2) show the userform as modeless

Userform2.show vbModeless

3) search google for untar. As i recall, winzip will handle tar files as
well. If not, maybe it was pkzip.
as for telnet, I would look for a telnet program that can be scripted.

Again, as I recall, Ron de Bruin had some code for working with winzip:
http://www.rondebruin.nl/tips.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top