Visual Basic :: Using A Spreadsheet Control In An Excel Userform

Permission Denied On Spreadsheet Control In Userform?
Hello,

I have a userform with a Chartspace 11.0 control (chRevenuesChart) and a Spreadsheet 11.0 control (shRevenues). I want the chart control to use the data from the spreadsheet control, which is hidden.

I'm using this code:

Code:
chRevenuesChart.Charts.Add
chRevenuesChart.DataSource = shRevenues
When I run it, I get an error on the second line: "Run time error 70, Permission Denied".

I've doublechecked the name of the spreadhsheet control, and it's shRevenues. I'm not sure if this is relevant, but I just installed the MS Office Web Components controls, because I used to run this program in MS Office 2000, and I'm now I'd like to get it working in Excel 2003. I used to have 9.0 controls in the userform, but they wouldn't open in Excel 2003 so I installed the Web Components update and then added in the new 11.0 controls to the userform.

Thanks for any suggestions,
Rina

Spreadsheet On Userform
Hey everyone, I am looking for a way to show, and if possible, edit a range of excel spreadsheet cells on a userform. Any help on this would help me immensely. I am using VBA/Office 97. I hope I explained this properly. Thanxs in advance!

Problem With Excel Spreadsheet Control
I'm using the excel spreadsheet control for vb6 to have a spreadsheet built-in to my form. I've figured out how to paste an entire list in using return-breaks and tab spaces(it's easier than trying to assign each cell individually, for my purpose anyway). I've even made it auto-fit all the info after doing so.

What I can't figure out how to do is make all the cells left-justify, so that the numbers are not right-justified and the text is left justified(looks better for my purpose if everything is justified the same way).

Anyone know how to do it? I've gone through all the actions in the pop-up menu for the control, etc. I can do it manually, but I really want it to to do it within the code I have that way it can save it for me rather than doing it manually and having to save it manually as well.

Creating A Control On A Excel Spreadsheet
Hi

Help please

I want to create a control ( a ComboBox specifically), during runtime, that will appear on a Excel spreadsheet.
My aim, as I've done many times before with a RFlexGrid, is to click on a cell and then the ComboBox appears over that cell with a selection of data.
Click on an entry in the Combobox and the Combobox disappears and the data appears in the cell !

Anyone know if this is at all possible ? If so, How do I do it.

Thanks

Pierre

How To Display Spreadsheet In Userform
Hi!

I have a userform that will post the values entered in the textboxes, to my spreadsheet. From there there are other cell that have formulas that calculate those values. The userform works great but I would like to display my spreadsheets in a new userform. I just want to avoid having the user view the spreadsheet on the application, however they could do it too.

Now I would like for the spreadsheet on my userform to display the one I already have, and if the user makes any changes to it from the userform, I wanted to save changes into the normal spreadsheet on the application.

Control Array Linked Up To An Excel Spreadsheet
I wish to make a row of data in MS-Excel 97 from every PictureBox created using a Control Array, in Form3. The code that I used to create the Control Array is as follows...

Private Sub Command1_Click()
Dim i As Integer
Dim picbox() As PictureBox

Dim top As Long

For i = 1 To 5
ReDim Preserve picbox(i)
Set picbox(i) = Me.Controls.Add("VB.PictureBox", "picbox" & CStr(i))
picbox(i).Visible = True
picbox(i).Picture = LoadPicture("c: esta.gif")
picbox(i).top = top
top = top + picbox(i).Height
Next i
Exit Sub

OnError:
On Error GoTo 0
0 Exit Sub

Exit Sub
End Sub

How can I get also data like a variable, which is called "Troop_Type" as the first column into the spreadsheet.

ADODC Control Linked To Excel Spreadsheet
I'm trying to link text box text to Excel spreadsheet data via an ADODC data control. I am having trouble finding STRAIGHTFORWARD information on how to set up the ConnectionString and RecordSource of the ADODC control to link it to the Excel file. I'm trying to set these up via the ADODC properties - not through direct coding.
Also, I would like to assign Excel cell values to the TextBoxes using the DataSource / DataField properties of the TextBoxes. After what I thought was a proper setup of the ADODC control, when trying to assign a cell value to the text box, there was nothing in the "DataField" property drop-down menu. So obviously I am missing something. I am trying to avoid assigning textbox values through direct coding. It was my understanding that the ADODC control was supposed to save you coding.
I was having luck doing this with the Data Control object, but I was trying to "upgrade" my application to the ADODC control.
If you could direct me to some useful information, I would appreciate it.

How To Open Excel File With Owc Spreadsheet Control
How can I open a excel file with OWC(office web component) spreadsheet control?

Pulling Data From Spreadsheet To Userform
Hello,

For various reasons (which I cannot change), I am having to use Excel instead of Access to essentially run a database.

I have it pretty much working how I want with all the code a buttons doing their job but I'm puzzled now with the final piece.

Access stores records which you can flick back and forth through after filtering. Is there a way to adapt this in Excel?

For example, if I wanted to review everything that was inputted on a certain date, it would actually pull that information from the rows onto the userform but have a scroll bar at the bottom to effectively scroll through all the "records" i.e. rows using the userform only.

I have been requested to limit the ability of users, as far as possible, to view the spreadsheet directly behind so need to find an alternative for them to filter and review multiple row entries.

I hope this all makes sense.

I'm afraid I don't have any code for this part as I've no idea where to even start. I've searched the net but can't find anything that quite fits my request.

Milari

UserForm & Spreadsheet Updating Problem
I have discovered what might become a serious problem for me with regards to using a UserForm to interface a Spreadsheet.

I created a UserForm to input and display data for a complicated spreadsheet. The spreadsheet is used to create a proposal for paver contractor. There are complicated formula. These formulas use cells that cacluate cost and materials needed for a job. The formulas use information from various cells to do the calculations. Changes to the top of the spreadsheet cell affects calculations towards the bottom. It works the same for changes at the bottom. They can affect cells at the top.

To make the form useful, I have a multipage form with 6 tabs. The user inputs quantity amounts or sq Ft and the amount of materials needed or price is calculated by the spreadsheet and then I display the value on the UserForm. The spreadsheet automatically updates all the cells when a value changes some place on the spreadsheet. But how do I update all my information when the cells change. For my UserForm to change the total price for a column of prices, I have to have an update for the total price for each of the sub items. For example: If there are 6 input line items, and the quantity triggers a cacluation for that line item, I get the total price from the spreadsheet and update the total price on the UserForm in each place that the quantity line item is UpDated.

I am new at programming VBA with Excel. How do make these kinds of updates? I hope I have been clear with what my problem is!

Thanks and If you have questions please ask them. I am not sure I can provide a code example. The spreadsheet is proprietary.

Thanks

Edit Spreadsheet While UserForm Is Open
I have a small program whitch is operated via Userform. Is it possible to edit values on the spreadsheet while UserForm (program) is open on the screen.

Converting Code For A Userform To Excel Sheet (control Array)
Hey. I searched the boards yesterday and found a method to create a control array in VBA using a seperate class and the .Tag property of each control. Now, searching again I can't find the link to where this was posted, but I do have the code.

It's something like this. This is the code in a UserForm.

Code:
Dim c() As New Classe1
Dim i As Integer

Private Sub UserForm_Initialize()
Dim obj As MSForms.Control
For Each obj In Me.Controls
If TypeName(obj) = "CommandButton" Then
i = i + 1
ReDim Preserve c(i)
Set c(i) = New Classe1
c(i).SetCommandButton obj
obj.Tag = i
End If
Next
End Sub

And this is the code for Classe1.

Code:
Private WithEvents cmb1 As MSForms.CommandButton

Sub SetCommandButton(ByVal cmb As MSForms.CommandButton)
Set cmb1 = cmb
End Sub

Private Sub cmb1_Click()
MsgBox cmb1.Tag
End Sub

Now, this works great, but I want to apply a similar reasoning to the actual Excel sheet and not just a popup user form. I know there is no ActiveSheet.Controls property, but ActiveSheet.OLEObjects exists instead. I tried to change the code to allow for this change, and I ran into two problems. First off, OLEObjects don't have a .Tag property, so that will have to be changed, and secondly the TypeName(obj) is always "OLEObject" and never the type of object it is.

Does anyone know how I can fix this?

How Link An Excel Cell With An ActiveX Control Put On The Spreadsheet
I'am beginning to write my own activex controls (in VB6).
These controls are used after on Excel Sheets
But if i understand how to use control's properties to get data, i didn't be able to make a property to link the control with a cell of the spreadsheet where is put the control.
(like does the natives controls of excel with "controlSource")
Could you help me please ?

UserForm With An MS Office Web Component Spreadsheet Object
I am wondering how you include references to cells in an underlying worksheet from within the formula of a cell in an MS Office Web Component Spreadsheet Object located on a UserForm?

I can copy in the value from my underlying range, but any attempt to set the .Formula string with references to the underlying worksheet results in errors.

Any help would be greatly appreciated. Thanks.

Code:
With UserForms.Item(int_window_ID - 1).Spreadsheet1
.ViewableRange = rng_window_range.Address
For Each c In rng_window_range
.cells(c.Row, c.Column).Value = c.Value
.cells(c.Row, c.Column).Font.Color = c.Font.Color
.cells(c.Row, c.Column).Font.Bold = c.Font.Bold
.cells(c.Row, c.Column).Font.Italic = c.Font.Italic
.cells(c.Row, c.Column).Interior.Color = c.Interior.Color
Next c
.cells(rng_window_range.Item(1).Row, _
rng_window_range.Item(1).Column).Select
End With

Formating Cell Border In UserForm Spreadsheet
Hi!

Could anybody help me with formating cells in UserForm spreadsheet.

My interest is to display some information from another sheet using spreadsheet in UserForm. I would like to have spreadsheets cell formated exatly the same as in the original worksheet.
I did some code, but can anybody help me to do it sorther and may be faster?
I have another problem I am still not able to download data from closed excel file.

Thanks in advance.

Boris

VB Code:
With UserForm6.Spreadsheet1.Cells(i, j - 7) .Value = Worksheets("FA").Cells(i, j).Value .Interior.color = Worksheets("FA").Cells(i, j).Interior.color .Font.color = Worksheets("FA").Cells(i, j).Font.color .Font.Size = Worksheets("FA").Cells(i, j).Font.Size - 1 .NumberFormat = Worksheets("FA").Cells(i, j).NumberFormat .HorizontalAlignment = Worksheets("FA").Cells(i, j).HorizontalAlignment .Borders(xlEdgeBottom).LineStyle = Worksheets("FA").Cells(i, j).Borders(xlEdgeBottom).LineStyle .Borders(xlEdgeTop).LineStyle = Worksheets("FA").Cells(i, j).Borders(xlEdgeTop).LineStyle .Borders(xlEdgeRight).LineStyle = Worksheets("FA").Cells(i, j).Borders(xlEdgeRight).LineStyle .Borders(xlEdgeLeft).LineStyle = Worksheets("FA").Cells(i, j).Borders(xlEdgeLeft).LineStyle .Borders(xlEdgeBottom).Weight = Worksheets("FA").Cells(i, j).Borders(xlEdgeBottom).Weight .Borders(xlEdgeTop).Weight = Worksheets("FA").Cells(i, j).Borders(xlEdgeTop).WeightEnd With

Editing Spreadsheet While Userform Is Loaded... *Resolved*
Trouble is that I am locked out of my excel spreadsheet when the userform is loaded - this means I can't scroll around and see what my program is doing - very bothersome. Is there some way to make it that the macro doesn't steal all the attention?

Thanks!

-----------------------------
looks like another bug...

Edited by - Shrout1 on 6/14/2004 7:29:32 AM

How To Open A Excel File With OWC(office Web Component ) Spreadsheet Control?
How to open a excel file with OWC(office web component ) spreadsheet control?

Pause Vba Userform, Manual Intput In Excel And Then Again With The Userform ??
hello you all, It's me again

I have had many probs, but the most are gone now, thx to you guys.

Next problem is this:

I use userforms to collect data from files and also user entries.
After a while the user should be able to enter some data manualy in the sheet, but the userform should be still active, because there are some global and other values i would like to keep.

Is it possible??

I would like to put some pause button in vba-userform and on the sheet a button to continue.

Thx 4 the help

Excel Spreadsheet From FTP To Customized Excel Spreadsheet
Hi guys!!

Help on this PLEASE!!

I get an EXCEL SPREADSHEET via FTP. I want to take that xls file and convert it to my own format which is ALSO an xls file. How can I do that? I went through your forums but cannot find what I am looking for. Basicly want to push a button and it must take the "rough" xls and create an new xls with the data "neat" so that I can use it.

For example the original xls has nonsense at the bottom and top like a address and names that I do not use, ONLY the "Item Codes" and "Item Description". Luckily the "Item Codes" and "Item Description" is in one column.

Excel - When Userform Visible, Excel Menu Unable To Be Accessed
I have an excel template that has a pop-up userform to enter different types of data. The issue is, when the form is open, I can no longer access the excel menu-bar. I would like to have this ability so that the user may copy and paste and infomation that maybe on another workbook open in the same window. How do I get this access back when the form is open?
Thanks,

Possible To Launch A UserForm In Excel From Startup Macro Without Showing Excel?
Greetings to all VBA/Excel Excperts!

I have a project at work that is requiring me to utilize Excel/VBA (something I'm not super-conversant in) and I am wondering if it is possible to have a UserForm be the first thing that pops up when you launch (i.e. double click on) a spreadsheet (likely an .xlt but not necessarily)? I know I can have an Autostart Macro (or something of that ilk) that could launch the form, but I am hoping there is a way to do this so that it just looks like I launched the UserForm and am not firing up the entire Excel program.

As soon as the functionality encapsulated in the UserForm is completed it will shut down Excel, which is why I'd rather just have the form and nothing else.

My reason for doing this is to make deployment of this "application" simple - just drop the spreadsheet onto the target system (which of course must have Excel 2003 installed, which all of our company systems do) and double click. Saves having to create an install package for a plain ole VB app.

Thanks for any suggestions and/or taunts you may have!

Invalid Data In Excel Spreadsheet/count Group Of Items In Excel Sprdsh
Please help:
Problem#1:
I populated an excel spreasheet with data from access table, using CopyFromRecordset function. The process was successful and all the fields displayed correctly in the spreadsheet, except the date field which displayed:"########". What did I do wrong?

Problem#2:
What vb code can I used to count group of items in the spreadsheet.
Any insight will be very helpful.
Thanks,
C.

Working With Excel Spreadsheet (*Resolved*No Excel Solution)
I haven't done any of this before, so I'm a bit stuck.
I need to create a small app that will allow the user to enter an item number, click find, and display the recordset to the user on a form. The info is stored in an excel spreadsheet. I would have done it using Access, but the user is across the country, doesn't have Access, and needs this thing by this afternoon.
Can anyone point me to something that will give me some VBA tips, or some instruction on how to do this?
I always seem to get install/access errors when I use Access db's, and with the guy being on the other side of the country, I want to avoid trying to work out all those problems. This should be a simple task, but I'm on a totally different train of thought program wise and can't seem to get into this Excel thing.

Thanks in advance.

Excel VBA Userform
Hello,

I am creating a userform in vba to input data into Excel. Column A of the spreadsheet has the house number. The Rows across the spreadsheet has the task codes associated with each house under construction. The form has each house number from column A listed. The user is suppose to enter the current task code associated with each house.

My Questions:

1. How do I get the labels on the form to read from the lot numbers on the first column?

2. The task codes are standard (95, 100, 105, 125 etc.). Each time a user enters a task code on the form, I want it to be able to input a date in the excel form of when that code was triggered. I am having a hard time matching up the lot numbers and task codes in the form with the lot numbers and task codes in the spreadsheet. Any suggestions?

Thank you in advance for your help.

Bobby G.

Excel Userform FAQ
Everything You Always Wanted To Know About Userforms But Were Afraid To Ask.

Lock Userform In Excel
Is there any way to lock the position of a userform in Excel once it has been launched?

Thanks!

VBA Need Minimize Excel From UserForm
I have a VBA application that displays a UserForm that has a Minimize button on it. When I click it, the UserForm minimizes as it should but the Excel workbook that contains the UserForm does not. I can not even click on the minimize button on the workbook because it is grayed out (I think because the UserForm has focus).

How do I make the Excel workbook that contains the UserForm that was launched also minimize when I minimize the UserForm?

Thanks,

Gary

Referencing To Excel Userform
Hi everybody,

I'm using VB6 and trying to get the value from a text box in an Excel userform. In my VB6 code, how do I reference to the Excel userform?

Hope you can help, many thanks.

UserForm And Excel To Work Together
Hi there,
I have a form and I would like the user to be able to work with the excell while the user form is still active.

How???

Thanks,
Eran

Add Excel Chart To Userform
How can I add a chart to a userform. I am playing with stock charts and wanted to take it off the workbook. I am creating a form to manipulate the chart. I want to change a variable and have that change reflected in the chart. I could just use the form over the chart page, but I was hoping to have everything in one place.

Thanks in advance,
---bnpatten
Richmond,VA

Excel Userform Steps Ok But
My Userform consists of quite a collection of various controls and was working OK. But, now when a particular checkbox is True, it is supposed to populate 5 textboxes with the values from another 5 (i.e. it copies address details from area to another) - it only refreshes the first textbox. When I step through the code it works fine....but if I Run the code it doesn't !

Excel UserForm Problem!
I can't seem to figure this out.... I have created an Excel UserForm to enter information from our various systems into a spreadsheet. Of course, I have to constantly hit Alt+Tab to switch back and forth from the UserForm to the other systems to retrieve the needed info. My problem is that when I Alt+Tab back into the UserForm, I can't figure out how to keep the cursor focused at the same spot as it was when I left! For example, say I'm entering a customer's street address from one system into the UserForm. I enter the address, 123, then switch over to my system to get the street name. When I switch back to my Excel UserForm, I then have to click in front of the "123" to begin typing again. Help!

Excel Sheet Within Userform
How can I browse sheets of current excel file within userform ?
I do not wish to show worksheet but browse it within a userform ...

Animated Gif In Excel-userform
hi

how can i insert an animated gif in an excel userform?

i allready tried to insert it as a normal picture but it just shows me the first picture of the gif-animation.

thank's a lot for your help

e.reum

Popupmenu And Excel Userform
I'm working in Excel and I use an invisible UserForm to hold some controls I'm using. On the spreadsheet there's some commandbuttons and I'd like to use popupmenu to pop up one of the form's menus. The problem is that there is no menu editor, and there is no PopupMenu method!

How else can I make a menu popup?

Menu Bar On Excel UserForm
Hello Freinds,

Is it Possible to create menu bar on Excel UserForm.

Thanks

SYED HAIDER ALI

Combox In Excel Userform
Hi,
1.I need to insert values to a combobx from a range in the worksheet.
2. After the user select a value from the combobox I want to insert the value to a Var.

Please help me.
Thanks,
Lilach

How To Add PictureBox On UserForm In Excel
How to add PictureBox on UserForm in Excel (if possible) ?

Email From VBA Excel Userform
Hi,
I've a relatively simple problem, but haven't been able to find the correct code for it. I have a userform that displays some information on it, and then, at the bottom, I want to put a mailto hyperlink. I've currently got a label, and coded the label_click event as a mailto;

Code:
Private Sub Label8_Click()
mailto: me@ mail.com
End Sub

but that doesn't work, it just fails. So I know there must be more to it than that. I can't seem to find the correct helpfile info, but I'm sure someone out there knows what to do.

Thanx,

déag.

Userform Control
I am trying to control a userforms properties based on worksheet settings. Form currently hides/shows columns based on checkbox input. At the same time, a text box is displayed in form indicating checkbox status. Is there a way to cause form to display current status of worksheet in the userform when it loads? or store the settings when last closed? Thank you in advance for your time.
Code for the Checkbox currently is:

Private Sub CheckBox1_Change()
With Me

If .CheckBox1.Value = True Then
TextBox1.Visible = True
Range("B1").Select
Selection.EntireColumn.Hidden = False
Else
TextBox1.Visible = False
Range("B1").Select
Selection.EntireColumn.Hidden = True
End If
End With

End Sub

Excel Spreadsheet
Code:
ChDir "C:WINDOWSDesktopDailyreport"
Workbooks.OpenText Filename:="C:WINDOWSDesktopDailyreport123.txt.TXT", _
Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
Array(9, 1), Array(10, 1), Array(11, 1))
I am saving daily information into separate textfiles and am building an excel interface so that a user can choose 3 or more days to build a graph..so now I need to put the textfiles into an excel workbook.
The above code will open up a text file and place it perfectly into its own excel workbook....but what I really want to do is.
Open up several of these text files and place them all into the same workbook (the one I am creating these from).
So that each textfile will go into its own spreadsheet in my workbook.
Any suggestions would be appreciated.

Non Excel Spreadsheet
I would like to find a control that is similar to a spreadsheet that allows me to edit any column. I found out that the listView or whatever it is called only allows you to edit one column. I do not want the users to have to have Excel. I also would prefer a small DLL if it is required. I guess I could design my own, but I think a pre-made control would make life much easier. Thanks.

http://www.xtremevbtalk.com/show...ht=spreadsheet

http://www.google.com/search?q=-Exce...&start=10&sa=N

EXcel Spreadsheet From VB
Hi
I have the following problem :
I have embedded a exel ole object in one of my forms.
Now within my program I turn it into a Ole Automation object.
eg. dim exelobj as object
set exelobj as OLE1.object
I then populate the neccessary cells.

all works fine up to this point.

I then try to save the exel sheet using the following command exelobj.worksheets(1).saveas "test.xls". It creates the exel file but the file is blank.

please help

Excel Spreadsheet In Vb6
I need to disable the right click menu at runtime on a spreadsheet. i am able to set the menu as read only, but how do completely remove the menu?

help

Excel Spreadsheet & VB
Hello to everyone:

I am very new at VB and programming. I am taking accounting and also, looking at taking programming while in school. At the moment, I am trying to create a simple function in an excel workbook. I wanted to use a listbox in a merged cell b8 to d8. With the choice being Worksheet (1), then the only other item in the listbox would be Worksheet(2). I wanted the listbox to show the list Worksheet (1), Worksheet (2). Action would be when you clicked on one Worksheet (1) it would show Worksheet(1) and then if you clicked on Worksheet (2) you would have the second worksheet show. I wanted the individual to be able to see the listbox when they opened the workbook. I am having a heck of a time getting the listbox to show any items in it in Excel. It is there and can't even click on it. I have tried while using the VBEditor in Excel putting this command in the Module, userform or any other place even on the listbox click(). I can not get it to show let alone work. I was wondering if any one would know or could help me here. I have even tried the groups from MS Office usergroup and instead get no answers and alot of spam to my email account.

With Worksheets(1)
Set lb = .Shapes.AddFormControl(xlListBox, 100, 10, 100, 100)
lb.ControlFormat.ListFillRange = "A1:A10"
End With

any help would be greatly appreciated.

Pamela

Using A Spreadsheet Control In An Excel Userform

HiCan someone help me? I want to use a spreadsheet control in an Excel userform and I want to load into it the data I have in one of the sheets in the same file where I am creating the userform. Is it posible? How? Thanks.

Hi

Can someone help me? I want to use a spreadsheet control in an Excel userform and I want to load into it the data I have in one of the sheets in the same file where I am creating the userform. Is it posible? How? Thanks.