Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Excel 2003 Userform && Combobox


I have created a userform in Excel with a combobox. I am trying to create a macro that will filter data on the spreadsheet by the option choosen in the combobox.

Here is the code I have now...


Code:


If ComboBox1 = "Testing" Then
Range("D12:D250").Select
'Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="=Test1", _
Operator:=xlOr, Criteria2:="=Test2", _
Operator:=xlOr, Criteria3:="=Test3", _
Operator:=xlOr, Criteria4:="=Test4", _
Visibledropdown:=False
End If

Unload Me

Range("A1").Select

End Sub



However, this does not work. I get some kind of error message. How many criteria can you have in the autofilter? What can I do to the code to get it to do what I need?

Is there a way to great a listbox, and get the data on the spreadsheet to filter/hide data that is not found in the listbox?

Any help with this issue will be greatly appreciated.

If any further information is needed, please let me know.




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Create A Userform At Runtime In Outlook 2003
I'm trying to create a USERFORM, in OUTLOOK 2003, at run-time which I can populate with a text box and several label boxes using VBA. These boxes will subsequently have data put in them (obviously!!)

I don't want to do this at design time as it needs to be a standalone macro that can be installed very easily.

The idea is that the macro will scan an email folder (currently the current open one) for certain email message formats and export certain information from these into Lotus Notes. That bit I can do, it's just creating the userform.

All the WWW links I found wanted me to buy their course or book for vast sums of money.

I'm sure this is possible just that I'm not sure how.

Anybody got any *sensible* ideas (and code samples would be even better)?

I've found a few for Excel but they don't work (OUTLOOK VBA objects to VBComponent).

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

Reading/Import Data From Excel 2003 File And Write/export To Access 2003 Database
I'm developing Database System using MS Access 2003
As the old data are all in Excel format, I'd like to add a function into the system so that operator import old data from Excel file into the Access 2003. But i do not know how to write data into Access Database using dataset object

I have write the follwing code to read data from Excel file:

Code: Public Sub import_from_excel()
Dim conn_excel As New OleDbConnection("Provider=Microsoft.Jet.Oledb.4.0;Data Source=c:
eport
eport_10102006.xls;Extended Properties=Excel 8.0")

sqlcommand = String.Empty
sqlcommand = "Select * from ['Multiple PN$']"
Dim excel_adaptor As New OleDbDataAdapter(sqlcommand, conn_excel)
Dim excel_DS As New DataSet
excel_adaptor.Fill(excel_DS)

'For testing
Dim excel_DT As New DataTable
Dim f_ed As New excel_data
f_ed.Show()
excel_adaptor.Fill(excel_DT)
f_ed.dg_excel.DataSource = excel_DT
'conn_excel.Open()
conn_excel.Close()
       
End Sub

Converting Word 2003 Table To Excel 2003 With VBA
I'm working on inputting project summary data into a spreadsheet.
The data is arranged in tables in Word 2003.
I've created sheets to store the information.

Currently, I have a form where the user specifies the path of the summary .doc file. Conversion is done thru VBA code
How do I get a specific piece of data into a specific field?

I'm thinking of crushing it into a text file and reading the field=value pairings.

I've zipped a sample summary and my .xls file

My manager wishes to automate the data entry process as much as possible

ANY help is greatly appreciated

Cheers,

ComboBox Userform
Currently i have an application with a userform that allows me to enter new data into a excel spreadsheet on the last empty row, and a scrollbar that allows me to view existing entries.

What i'm trying to do now is to be able to edit existing entries.

I Have a Dropdown Combobox Set to a range in column "A" that allows me to select existing entries. That works fine although i would like to filter it in ascending order. "Don't know if it's possible"


Code:
Private Sub CboBxDealer_DropButtonClick()
CboBxDealer.List = Range("DealerName").Value
End Sub

But what i'm having trouble with is updateing the various textboxes on my userform based on the combobox selection so that i can edit the data in the textboxes.

Next after i edit the data in the textboxes, i would like to click a command button to update each cell in that row of data, possibly using the combobox.controlsource property

Thanks for your time.

Fill More Then 0ne Combobox In A Userform
Hello.

I'd like to fill 4 comboboxes on a userform. The problem is i don't know how to loop through the 4 combobox object.

Thanx in advance.

Werner

my code:

Code:
Private Sub UserForm_Activate()

Dim cmb As ComboBox
Dim cmbo() As ComboBox

fileGrootBoek = "C:XXXXXurenverantwoordingGrootboek.xls"
qry = "SELECT nr, grootboek FROM `Grootboek$`"

'get a handle on all comboboxes
For Each cmb In frmGrootBoek
Set cmbo(i) = cmb
Next

'counters
i = 0
j = 0

'Fill form with values from excel sheet
Call test

'Execute query
Set result = Gegevens.QrySelect(fileGrootBoek, qry)

'Fill comboboxes wich are named: cmbGB1, cmbGB2, cmbGB3 and cmbGB4
'First empty combobox
For Each cmb In frmGrootBoek

cmbo(j).Clear

'code to fill combobox
Do While (result.EOF = False)

cmbo(j).AddItem (result.Fields(1))
cmbo(j).List(j, 1) = result.Fields(0)
j = j + 1
result.MoveNext

Loop

Next

'close recordset
result.Close
Set result = Nothing

End Sub

Userform ComboBox Problems
I can't seem to pinpoint the command to let me link one combo box to another so that when I select a certain option it will enable ComboBox2 I have on the userform, and if I select something else it will enable ComboBox3 on the userform. I have figured out how to do this with option buttons, but the combo box always has baffled me.

Combine 3 Userform Combobox Inputs?
I have a user form with multiple comboboxes.
one for customer
one for purchase order
one for customer assembly

can these three items be combined and shown on the userform in a text box, or something like that?
I need to make a Unique ID from these three categories.

Error Initializing Data In UserForm ComboBox
Hi,

I can't seem to get my userform's combobox to correctly initialize the data range I'm inputting.  I've inserted my code below.  When I run the program I don't get any errors.  However, the combobox only contains the first value and nothing else.  Somehow the other values in my array are not being put into the data list for the box.  Any ideas on what I should do?




Private Sub UserForm_Initialize()

'Set Range for RVArray
Dim RVvar As Integer
    Sheets(1).Range("B5").Select
    Selection.End(xlDown).Select
    RVvar = ActiveCell.Row
    

'Get info for RVArray
Dim RowCounter As Integer
Dim RVArray() As String
ReDim RVArray(1 To RVvar)
    
    RowCounter = 4
    Counter = 1
    
    For RowCounter = 5 To RVvar
        RVArray(Counter) = Worksheets(1).Cells(RowCounter, 2).Value
        Counter = Counter + 1
    Next
    
    RV_Sub_System_ComboBox.Column() = RVArray


'Combobox startup properties
    RV_Sub_System_ComboBox.Style = fmStyleDropDownList
    RV_Sub_System_ComboBox.BoundColumn = 0
    RV_Sub_System_ComboBox.ListIndex = 0

End Sub

Change The Data Format Of A Combobox In A Userform
I am trying to format the date values as returned by a combobox selection. The selection is in "mmmm yyyy" format in Excel, but when a date is selected from the combobox on the userform, the format changes to the number format eg. 39426. I need the user to see the date they have selected. Can anyone help me solve this one please - I am new to VBA and have tried a number of things but cannot find a working solution. Thanks

Elaine

Is It Possible To Link To The Rows Of A Closed WorkBook With A UserForm ComboBox?
Re: Is it possible to Link to the Rows of a Closed WorkBook with a UserForm ComboBox?

Actualy it would be fine to populate Sheet 1 of my ActiveBook from Sheet 1 of the closed WorkBook. I know how to create and configure the UserForm and ComboBox to display the Data from Sheet 1, probably just 20 rows and 5 colums(out of 10 columns, 2,500 rows total). - I just need to know how to make the link between the Active & External Sheets and the code to extract the relative column 10 data, that I (CLICK ON), from the DropDown List. - I need the result of that CLICK, TO copy the value of the relative column 10 cell, and place that value, in cell ("a1"). - In summation, it is just the workbook (sheet 1) linking and the code to get that relative column 10 cell value into cell a1, that I need. The rest, I'm familiar with

If it's too major of a task, I am willing to open both WorkBooks, just prefer not to, if it's not too involved. - Thanks in advance - Frank

VBA Access 2003 Combobox Clear
Good afernoon.

I was going through some of my DB code and I noticed some irregularities.

In certain events I update the contents of a combobox as follows:

* cmdName.Text = ""
* cmbName.Value = ""
* Me.cmbName = ""

I know .Text and .Value are different, but not sure of the difference in this situation.

What I am attempting to do, is clear out the contents of the combobox the most efficient way. Does using the Me with the cmbName make a difference?

Thank you,

WHEELS

Re-distributing Excel Viewer 2003 And Word Viewer 2003
Hi,
Does any of u know how to re-distribute Excel Viewer 2003 and Word Viewer 2003? As far as I learnt, the core files are different than Excel/Word Viewer 97?

Has anyone succeeded re-distributing them with his application?

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!

Playing Sound With VBA In Excel 2003 Using SndPlaySound32 Doesn't Work In Excel 2007
The following worked in Excel 2003:
Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias "sndPlaySoundA" _
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Call sndPlaySound32(WelcomeFilenames(filenumber), 1)

Why doesn't it work in Excel 2007? Is there a way of using a single play .wav sounds command that will work in both Excel 2003 and Excel 2007?

I tried googling around and searching in these forums, but could not find anything related. If this has already been discussed, please redirect me to the appropriate location.

Thanks for your help.

Closing Excel Process After Creating Excel.Application From Access 2003
After such code:
Code:
Dim xLs As Excel.Application
Set xLs = New Excel.Application
xLs.Visible = True
xLs.Workbooks.Open DtR
‘do stuff
xLs.ActiveWorkbook.Close savechanges:=False
Set xLs = Nothing
There is always Excel process left running. When I do this 5 times I will have 5 instances of Excel running.
Is there a way to kill this process at the end of the code?

Microsoft Office Object Library Excel 2003 And Excel 2000
Ciao Gurus,

I have developed an application with excel 2003 and, of course, it takes as reference the library of excel 2003, but most of the users have excel 2000 and the application goes in error when this reference is called. The library is Microsoft Outlook that in excel 2003 is 11.0 version while in excel 2000 is 9.0.
How can I fix the problem?
Best Regards
KAIALA


 

Edited by - kaiala on 7/2/2007 3:21:56 AM

VBA Excel 2007 Different Than Excel 2003 For Inserting 255+ Textbox Characters
My VBA application that works fine in Excel 2003 does not work in Excel 2007.

For example, the below code works fine in Excel 2003 to display multiple lines within a textbox:
Set DiagramTextBox = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, ActiveSheet.Shapes(PM.ActivityCode(PM.TotalActivities)).Left + 55, ActiveSheet.Shapes(PM.ActivityCode(PM.TotalActivities)).Top + 25, 330.75, 130.75)
DiagramTextBox.TextFrame.Characters(1).Text = "The activities seen in red are on the current critical path(s) based on what crashing (if any) has been done." & vbCrLf & vbCrLf & "Choose a set of critical path activities to crash that TOGETHER cost the least amount to shorten ALL critical paths." & vbCrLf & vbCrLf & "Needless to"
count = DiagramTextBox.TextFrame.Characters.Count
DiagramTextBox.TextFrame.Characters(count + 1).Insert String:=" say, you need to look at the crash cost per period (in the 'CC Per Period' column in the other window), and you need to verify that each activity can still be shortened (compare the 'Activity Time' to the 'Crash Time (CT)')."

In the VBA debugger in Excel 2003, the count variable says that there are 244 characters in the textbox after the first assignment of text but before I use the Insert method to get around the limitation that Excel seems to have inserting more than 255 characters at a time to a textbox.

When I try to run the same code in Excel 2007, I get error 1004 when I execute the line for "DiagramTextBox.TextFrame.Characters(1).Text". Even though I used "Option Base 1" at the start of my module, it seems to want to use 0-based indexing. Why is this?

It seems as though I can change the line to "DiagramTextBox.TextFrame.Characters.Text" and it will work fine in both Excel 2003 and Excel 2007. But then as I step through the VBA debugger in Excel 2007, I get to the line that counts the characters in the textbox, and it says there are only 240 characters (not 244, as with Excel 2003)! The textbox displays the text correctly, though. When I try to enter the line "DiagramTextBox.TextFrame.Characters(count + 1).Insert String:=", I get another error 1004. If I change from "count + 1" to just "count" and use:
DiagramTextBox.TextFrame.Characters(count).Insert String:=" say, you need to look at the crash cost per period (in the 'CC Per Period' column in the other window), and you need to verify that each activity can still be shortened (compare the 'Activity Time' to the 'Crash Time (CT)')."
it will execute, but then the text in the textbox will incorrectly say, "Needless t say,... ". (Notice that it dropped off the letter o).

How can I write VBA code that will work correctly in Excel 2003 and Excel 2007 for inserting more than 255 characters into a textbox?

Thanks for your help.

Loading Files In Excel 2000 Vs Excel 2003
Is there something different when loading files using Excel 2000 vs Excel 2003? My code works under Excel 2003, but it doesn't work under Excel 2000. The program crashes.

Here's the code:


Code:
Private Sub Form_Load()

Set xlApp = New Excel.Application
If Dir$("C:PrePaid LogPrepaid-Log.xls") <> vbNullString Then
MsgBox ("Prepaid-Log.xls was successfully found.")
Set xlWB = xlApp.Workbooks.Open("C:PrePaid LogPrePaid-Log.xls")

Else
MsgBox ("Prepaid-Log.xls was not found. A new file will be created.")
Set xlWB = xlApp.Workbooks.Add
End If

End Sub

Please help.

Thanks

Start Excel 2002 From Excel 2003
I have an application that runs OK on excel2002 (excel vers. 10) but breaks in excel 2003 vers 11.

I would like to make a temporary fix until the problem has been solved.

I sub-class Excel.11 and use the workbook_Open event to see if it is my (problematic) type of book, then Open an instance of the excel.10 and open the book in it.
Any ideas on how to due this, and the code below will just open the same excel.11 application.
(Excel 10 and 11 seems to have the same class name).

Code:
pseudocode
...
if(isProblematic(WB)) then
Set oExcel10 = CreateObject("Excel.application") ' This is where I need help.
With oExcel10.workbooks.Open(WB.fullname)
end if
...

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.

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

Customized Help Assistant For Excel Userform
Hi, I have created a help asisstant for my userform in Excel which works fine except I dont know how how to create the project file so it works like the proper windows help. Does anyone know how, or where I can find out how to do this? Anything would be greatly appreciated.

Excel Ranges Of Cells Into UserForm?
Hi,

I was trying to display ranges of data from
Excel works sheets into the userform, within the excel;s macro files.

For example;

There is a score board from
"G16:G43" (This is a row)

and I want that to be placed in my viewing form
all arranged by itself.

Is this possible?

Thanks in advance

Using RowSource (and An Excel ListObject) In A PPT UserForm...
I'm writing a series of macros that search through a database (Excel ListObject) when the user clicks on an Autoshape during a PPT presentation and then shows a userform with the results.

Right now I'm creating a new, global Excel.Application Object every time the Autoshape macro is run. The database file is then opened and searched (the results go into another ListObject). When I try to populate my form's ListBox, however, I get this error: Run-time error '380': Could not set the RowSource property. Invalid property value.

Here's my code, any input appreciated:
Code:
Public Sub SRForm_Reload(Target As String, DatabaseFile As Excel.Workbook)
Dim SearchResults As Excel.Worksheet
Dim ResultsList As Excel.ListObject

Set SearchResults = DatabaseFile.Worksheets("Search Results")
Set ResultsList = SearchResults.ListObjects(1)

' don’t know if I need to hide and re-show form everytime; this is so that the form seems
' to “snap” into its new configuration everytime SRForm_Reload is called
ResultsListBox.Hide

' set column count and column headers
With ResultsListBox
.ColumnCount = ResultsList.Range.Columns.Count
.ColumnHeads = True
End With


DatabaseFile.Windows(1).Visible = True

' populate ResultsListBox with values from SearchResults.ListObject(1)
ResultsListBox.RowSource = "'Search Results'!" & ResultsList.DataBodyRange.Address
' for example, my test case should evaluate this statement as
' ResultsListBox.RowSource = 'Search Results'!$A$2:$M$3

DatabaseFile.Windows(1).Visible = False
ResultsListBox.Show
End Sub

This code works fine in Excel; I figured that I could just import it and make a few changes to unqualified references as shown HERE. Obviously that's not the case.

I assume that I'm referencing "Search Results" incorrectly. FYI, the file I'm working from is PAIN_Temp.xls ( aka oExcel.Workbooks(2) aka DatabaseFile).

any ideas?

Hide Excel Whilst In UserForm
Hi, am I able to hide excel whilst in a userform? And if at all possible if the user tries to open another spreadsheet it opens in a new instance of excel?

I am using Excel 97 by the way...

Cheers muchos!

Got Trouble Subclassing MS Excel's UserForm
Hi everyone!
I’ve tried hard to subclass MS Excel’s UserForm window correctly, but when I try to open another workbook double clicking on it while UserForm is being hidden and MS Excel is being minimized, MS Excel crashes!!! By the way, if I open another workbook with File->Open or when MS Excel is not minimized everything goes fine...

Any suggestions??

Here's the sources...

Code:
'----------------------------------UserForm code--------------------------
Option Explicit

'API declarations
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongA" ( _
ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
'Constants
Private Const GWL_WNDPROC As Long = -4

Private Sub btnHide_Click()

Me.Hide

End Sub

Private Sub UserForm_Initialize()

hUserForm = FindWindow(vbNullString, Me.Caption)

If hUserForm = 0 Then
MsgBox "Failed to get window's handle", vbCritical, "Error"
Unload Me
Exit Sub
End If

lPrevProc = SetWindowLong(hUserForm, GWL_WNDPROC, AddressOf WindowProc)
If lPrevProc = 0 Then
MsgBox "Failed to subclass window", vbCritical, "Error"
Unload Me
Exit Sub
End If

bClose = False

End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

bClose = True
SetWindowLong hUserForm, GWL_WNDPROC, lPrevProc

End Sub
'--------------------------------------------------------------------------
'--------------------------------------------------------------------------

'------------------------------'Subclass' module----------------------------
Option Explicit

'API declarations
Private Declare Function CallWindowProc Lib "user32" Alias "CallWindowProcA" ( _
ByVal lpPrevWndFunc As Long, ByVal hWnd As Long, ByVal Msg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
Private Declare Function DefWindowProc Lib "user32.dll" Alias "DefWindowProcA" ( _
ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Private Declare Sub PostQuitMessage Lib "user32.dll" (ByVal nExitCode As Long)
Private Declare Function DestroyWindow Lib "user32.dll" (ByVal hWnd As Long) As Long
'Constants
Private Const WM_CLOSE As Long = &H10
Private Const WM_QUIT As Long = &H12
Private Const WM_DESTROY As Long = &H2

'Variables
Public lPrevProc As Long
Public hUserForm As Long
Public bClose As Boolean

Public Function WindowProc(ByVal hWnd As Long, ByVal uMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
'-----------
'Method one
'-----------
Select Case uMsg
Case WM_CLOSE
DestroyWindow hWnd
WindowProc = 0
Case WM_DESTROY
PostQuitMessage 0
WindowProc = 0
Case Else
WindowProc = CallWindowProc(lPrevProc, hUserForm, uMsg, wParam, lParam)
End Select

'-----------
'Method two
'-----------
'WindowProc = CallWindowProc(lPrevProc, hUserForm, uMsg, wParam, lParam)


'-----------
'Method three
'-----------
'WindowProc = DefWindowProc(hUserForm, uMsg, wParam, lParam)
End Function
'--------------------------------------------------------------------------
'-------------------There are the methods I'd tried to subclass--------------

'-------------------------------Workbook----------------------------------
Option Explicit

Private Sub Workbook_Open()

UserForm.Show

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Not bClose Then
UserForm.Show
Cancel = True
End If

End Sub
Edit by italkid: Please use the [vb]][ and [/vb] tags to display your code, thanks.

Excel VBA UserForm Will Not Load Or Show
Hi,

I would very much appreciate some advice or knowledge from anyone who may have expereinced this problem or may know a work around.

I am using excel 2000, the vba application that I am writting has to run on excel 2000 so upgrading would not be an answer to my problem.

I am writing a VBA application that is 100% dialog based, in terms of the UI.

Up until today I used a worksheet in the book, with buttons placed on the sheet to launch the custom userforms. Everyone of the user forms works fine when launched from the worksheet buttons or from another userform that has been launched.

Today I took some steps to remove user access to the workbook, so I took all the functionality of the worksheet that held the buttons to launch the applications userforms and added the same functionality to a modal user form that autoloads.

Strangeness occured, two of my forms will not show themselves when lanched from this new front page, the .show method has actualy dissapeard from the userform object when used in the new frontpage userform module and attempting to use either load or show method produces an "object could not be loaded" or "method does not exist" erro respectivley.

here is the front page:


Code:
Private Sub ActivityManager_Click()
SpecialsManager.Show 'works
End Sub

Private Sub CourseManager_Click()
CourseManager.Show 'fails
End Sub

Private Sub ExitWebBuilder_Click()
Application.Quit
End Sub

Private Sub HelpButton_Click()
HelpForm.LoadTopic ("The Front Page")
HelpForm.Show 'works
End Sub

Private Sub MaintenanceMode_Click()

Application.DisplayFullScreen = False
Application.CommandBars("Full Screen").Enabled = True
Application.CommandBars.ActiveMenuBar.Enabled = True
Application.EnableCancelKey = xlEnabled
Worksheets(1).Protect UserInterfaceOnly:=False
Application.DisplayAlerts = True

Worksheets("File Types").Visible = True
Worksheets("Activities Record").Visible = True
Worksheets("Activities Media").Visible = True
Worksheets("Positions").Visible = True
Worksheets("Staff Media").Visible = True
Worksheets("Opps").Visible = True
Worksheets("Student Web Builder").Visible = True
Worksheets("Staff Record").Visible = True
Worksheets("Resedence Record").Visible = True
Worksheets("Courses Record").Visible = True
Worksheets("System Settings").Visible = True
Worksheets("Course Media").Visible = True
Worksheets("Residence Media").Visible = True

Worksheets("Student Web Builder").Activate

Me.Hide
End Sub

Private Sub ResidenceManager_Click()
ResManager.Show 'works
End Sub

Private Sub StaffManager_Click()
StaffManager.Show 'fails!
End Sub

Private Sub StudentManager_Click()
StudentManager.Show 'fails !!

Show StudentManager 'fails
End Sub

Private Sub UserForm_Activate()
Call AddMinimize(ActiveControl.parent)
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then 'prevent user closing form
Cancel = True
End If
End Sub
now I tried replacing StudentManager.show with

UserForms.Add("StudentManager").Show

which will open the userform I want but causes a "must hide foremost modal form first" error when I dismiss it, with no forms above it in the z-order?

Also if I bypass the front page dialog, and use the buttons embeded in the worksheet I can still open any userform I want, to add to that I can open any of the userforms that wont open from 'frontpage' from any form that will open from it!

Ive tried unloading all forms and modules and reloading with no success and have searched msdn for hours which yielded no information.


I am lost so any advice would be very much appreciated.

Thanks in advance
Gav

Excel VBA: Can A UserForm Be Used As A Program Menu?
Can a UserForm (with Button Controls) be used a macro menu?

What I want to accomplish:
1. minimize the main coding and run procedures with subroutines.
2. Use a UserForm as the MainMenu to call all procedures and return back to the MainMenu (having a command button as an exit).

Something like this:
1. Initialize Routine
2. MainMenu (i.e.: MainMenuForm.Show)
3.Close Routine

Problems I am having:
1. Only showing one Userform.show command. (user may decide to do only one or a number of procedures). Right now I have to show a number of .show commands in succession to accomplish every procedure possible. As you can tell this is not the best way to do this---can it be looped somehow to limit the coding, with an exit on selection of the "quit" command button selection.
--Would the .hide statement be best for this issue?

Userform On Load Question Excel-vba
I've developed a prototype of my userform.

I have hidden the excel sheet.

I would like to have the userform load upon opening the workbook.

Here's my top lines of code


Code:
Private Sub UserForm_Initialize()
ActiveWindow.Visible = False
End Sub


Private Sub startup_Onload()

Show.UserForm.frmClientInput

End Sub

Saving An Image Of A Excel Userform
This is probably an easy one, but i'm baffled again. I have an excel VB program where the user can create various charts and calculate data on various userforms. I have figured out how to allow the user to save any charts created as .GIF files. The .GIF files are saved to a predetermined path and filename into a user created session folder. What I can't figure out is how to take a snapshot of a userform and save it as a .GIF file.

So....

Does anyone know how I can set up a commandbutton in Excel to save a snapshot of an active userform to a *.GIF file with a predetermined string name and path? (I think this would be much easier if I was in Access rather than Excel)

Thanks in advance for any advice, Whoiss

Simple Userform Linked To Excel
Hi everyone,

This is my first post here and if I'm honest my first dable with VB, i.e. I'm a complete novice so please excuse my total ignorance but if anyone could help with this little query I'd be really grateful!

I have made quite a basic UserForm in VB after about 3 hours lol this form allows users to enter information and when it is submitted it creates a new worksheet in excel and plops in the info. I don't know how complicated this is to do but I would like to put some code in there which would give each column a header, obviously these correspond to the form fields. Here is my code...


Private Sub cmdAdd_Click()
Dim iRow As Long
Dim NewSheet As Worksheet
Set NewSheet = Worksheets.Add
NewSheet.Name = txtInitiative.Value

'find first empty row in sheet
iRow = NewSheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

'copy the data to the sheet
NewSheet.Cells(iRow, 1).Value = Me.txtInitiative.Value
NewSheet.Cells(iRow, 2).Value = Me.txtCurrentstate.Value
NewSheet.Cells(iRow, 3).Value = Me.txtFuturestate.Value
NewSheet.Cells(iRow, 4).Value = Me.txtKeymilestone.Value
NewSheet.Cells(iRow, 5).Value = Me.txtMetricsofsuccess.Value
NewSheet.Cells(iRow, 6).Value = Me.txtLeadadmin.Value
NewSheet.Cells(iRow, 7).Value = Me.txtProjectmngr.Value

'clear the data
Me.txtInitiative.Value = ""
Me.txtCurrentstate.Value = ""
Me.txtFuturestate.Value = ""
Me.txtKeymilestone.Value = ""
Me.txtMetricsofsuccess.Value = ""
Me.txtLeadadmin.Value = ""
Me.txtProjectmngr.Value = ""
Me.txtInitiative.SetFocus

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub Image1_Click()

End Sub


Any help at all would be greatly appreciated! The headers would be Initiative, Current state, Future state, Keymilestone, Metrics ofs uccess, Lead admin & Projectmngr.

Thanks everyone

VB Excel Userform - Print In Landscape
Hello

I am having trouble printing my user form in landscape view. here is my code...
Private Sub cmdPrint_Click()
With InputForm
.PageSetup.Orientation = xlLandscape
.PrintForm
End With
End Sub


"Method or Data member not found" this is the error message i am getting..

thank you in advance...

Pretty Gal

Copyright © 2005-08 www.BigResource.com, All rights reserved