Free Microsoft Excel 2013 Quick Reference

Controlling sheets using userform Results

I am creating a program where I need a variety of buttons and check
boxes that do stuff to the excel sheet. Just using the excel forms
doesn't work because when I zoom out (which the program needs to be
able to do), the buttons get small and unreadable.

So, I would like to be able to create a floating toolbox that has the
buttons and checkboxes in it. I tried a UserForm, but it seems that the
userform requires itself to be deactivated before excel can calculate
anything. Is there any way to have a userform where the worksheet can
be calculated (I am using manual calculation) while the userform is
still up and active).

The goal is to have a floating Start/Stop button that controls whether
or not the worksheet is calculating or not.

Thanks in advance,

Abe

Hi,

I'm using a userform to capture data and there on display them in various cells in a sheet. For storing the data before displaying in the cells, I am using a multidimensional array. Now, the problem I am facing is that I have a combobox and a listbox whose values I want to keep on adding into the multidimensional array till the user clicks the submit button. The code I am using is :

Dim arr() As String
Dim oName As String
Dim oJob As String
Dim cName As Integer
cName = lbColNames.ListCount
oName = txtColName.Value
oJob = cbJob.Value
If cbJob.Value = "" Then
    MsgBox "Please select a Job."
    cbJob.SetFocus
Else
    lbColNames.AddItem (oName)
    ReDim Preserve arr(cName, cName)
    arr(cName, cName) = oName
    arr(cName, cName + 1) = oJob
End If
But this generates an error "Subscript out of range". What wrong am I doing here? And how to insert values from 2 different controls into one multidimensional array? Please help.

Peace,

Shivboy

Trying to update an old Excel 95 template in Excel 2000 version. Part of the
code is dealing with DialogSheets which, I found out, were replaced by
userforms. When this ran in 95, the caption of the dialog box would change.
Essentially the same dialog box was used but the controls changed based on
the status.

Does anyone have a suggestion for changing this to userforms? Do I need to
create 2 different userforms and then somehow code it dependent on that?

Below is the code:

Sub LockSheet()
'Controls the Lock Sheet button on the Customize page
Const LockDlg = "Lock"
Const Lock_String = "Lock/Save Sheet"
Const Lock_Text = "You can lock the information on the Customize page
and save your customized version of the template."
Const Unlock_String = "Unlock This Sheet"
Const Unlock_Text = "By unlocking this sheet, you enable changes to be
made to the information on the Customize sheet. Select ""Lock This Sheet""
after you make your changes to protect the sheet from accidental changes."
Const Save_Alrt = "Your new customized template has been saved to the
default directory. "
Const Save_Alrt2 = "To begin using the invoice, double-click the
shortcut in Pioneer Applications folder."
Const Save_Filter = "Templates,*.xlt"
Const Save_Title = "Save Template"

If Sheets(Vital).DrawingObjects("Lock").Caption = Lock_String Then

If DialogSheets(LockDlg).Show Then
Sheets(Vital).Protect DrawingObjects:=True, Contents:=True
Sheets(Vital).DrawingObjects("Lock").Caption = Unlock_String
Sheets(LockDlg).DialogFrame.Caption = Unlock_String
Sheets(LockDlg).TextBoxes("PNL1_TXT1").Text = Unlock_Text
Sheets(LockDlg).GroupBoxes("PNL2").Visible = False
Sheets(LockDlg).OptionButtons("LCK_1").Visible = False
Sheets(LockDlg).OptionButtons("LCK_2").Visible = False
Sheets(LockDlg).TextBoxes("PNL1_TXT1").Height = 80
If Sheets(LockDlg).OptionButtons("LCK_2").Value = xlOn Then
ThisDir = CurDir()
TempDir = Application.TemplatesPath
ChDrive Mid(TempDir, 1, 1)
ChDir TempDir
FileNm = Application.GetSaveAsFilename(FileFilter:=Save_Filter,
Title:=Save_Title)
If FileNm <> False Then
OWFlg = Application.DisplayAlerts
Application.DisplayAlerts = False
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets(Content1).Activate
Sheets(Vital).Visible = False
With ActiveWorkbook
.SaveAs Filename:=FileNm, FileFormat:=xlTemplate
FName = .FullName
PName = .Path
End With
Application.DisplayAlerts = OWFlg
MsgBox Save_Alrt & PName & Save_Alrt2, vbOKOnly + vbInformation,
SheetBar
ThisWorkbook.Close
End If
ChDrive Mid(ThisDir, 1, 1)
ChDir ThisDir
End If
End If

Else

If DialogSheets(LockDlg).Show Then
Sheets(Vital).Unprotect
Sheets(Vital).DrawingObjects("Lock").Caption = Lock_String
Sheets(LockDlg).DialogFrame.Caption = Lock_String
Sheets(LockDlg).TextBoxes("PNL1_TXT1").Text = Lock_Text
Sheets(LockDlg).GroupBoxes("PNL2").Visible = True
Sheets(LockDlg).OptionButtons("LCK_1").Visible = True
Sheets(LockDlg).OptionButtons("LCK_2").Visible = True
Sheets(LockDlg).TextBoxes("PNL1_TXT1").Height = 40
End If

End If

End Sub

Thanks for any input.
Marcia

I have made a programme in Excel 2000 using VBA and a number of sheets.
Indata to the programme is at some instances entered from User forms.
The User forms are activated by clicking a Command Buttons. While
trying to activate the User forms in Excel 2003, an error message
occured: "Compile error in hidden module: Sheet 15".
Changing security level to low was not enough.

I became aware of that an ActiveCGM Control was missing. After
installing the file acgm.dll, the programme was running on computers in
my house, but problems still occured on other computers.

Are there any other settings I need to change/add in Excel 2003?
And is this acgm.dll specific for specific versions of Excel?

Beste regards
Thomas

Good aftrnoon and thank you in advance for any help.

I have created a code that I am using in a combobox to pull information into my userform from a worksheet. I am using the match function for a specific worksheet. What I would like to do, is be able to use the match function to pull the information into my userform based on the the current active worksheet, instead of having to specify the worksheet and creating a userform for each month (which right now is the only way I can think of because I can't seem to figure that part out My workbook is set up by month. Here is the code I created. Any help would be much apprecitated. TY...Sandy

Dim testales As Long
Dim x As Variant
  Dim Response As VbMsgBoxResult
Dim lRow As Long
Dim ctlinfo As Control
 Dim C As Range


txtempno1 = Sheets("Master").Range("A" & gmsales1.ListIndex + 2)
   txtsales1 = Sheets("Master").Range("B" & gmsales1.ListIndex + 2)
    txtlocation1 = "GM"
    
Sheets("April").Activate
testsales = gmsales1.Value
x = Application.Match(testsales, Worksheets("April").Range("c1:c17"), 0)
  
If IsError(x) Then
  
           MsgBox "No current record exsist, continue recording new record.", vbInformation + vbOKOnly
 Me.txtunits1.Enabled = True
 Me.txtpvrtot1.Enabled = True
Me.txtemp1.Enabled = True
Else
    Me.txtunits1.Enabled = False
    Me.txtpvrtot1.Enabled = False
    Me.txtemp1.Enabled = False
  
testsales = gmsales1.Value

With Sheets("April")
    ' row determination
    Ctr = Application.Match(testsales, Worksheets("April").Range("c1:c17"), 0)
    
    If Application.Match(testsales, Worksheets("April").Range("c1:c17"), 0) Then
            ' populate TextBox2
            Me.txtunits1.Text = Application.Index(.[d:d], Ctr, 0)
            Me.txtpvrtot1.Text = Application.Index(.[f:f], Ctr, 0)
    Response = MsgBox("A record already exisit for:       " & gmsales1.Value & vbCrLf & ("Do you
wish to edit record."), vbQuestion + vbYesNoCancel)
 If Response = vbYes Then
 gmfrmedit.Show
 ElseIf Response = vbNo Then Exit Sub
 

For Each C In Range("A1:A17" & Cells(Rows.Count, "A").End(xlUp).Row)
If Not IsError(C) Then
    If C.Value = "True" Then
        Range("A" & C.Row & ":G" & C.Row).Interior.ColorIndex = 6
    Else
        Range("A" & C.Row & ":G" & C.Row).Interior.ColorIndex = xlNone
    End If
End If
Next C

 
 
 
 End If
    End If
    End With
    End If
End Sub


I have a question about the excel programming

I would like to write a function which is used in different Userforms

The purpose of this function is that
When I enter the code into the "textbox" provided,
the function name_selected would be called The function would automatically search the "name" according to the "code" provided from Sheet3 Then the value would be showed in ResultTextbox

Call name_selected(103, "UserForm2", "Textbox3")

PS: 
103 is the code entered by the user
UserForm2 is the UserForms using this function
Textbox3 is the specified Textbox in specified UserForm to show the result

Sheet 3
ColA   ColB
101  Peter
102  Mary
103 Jessie
The following is the code in VBA:


Function name_selected(Code, Form, ResultTextbox)

' Explanation
' Code=The code entered by the user
' Form=The UserForm running this function
' Textbox=The Textbox showing the result 

Dim uf As UserForm

Set uf = Form

name = Application.WorksheetFunction.VLookup(Code, Sheet3.Range("A:B"), 2, False)

uf.Controls(ResultTextbox).Value = name
End Function

When I run this function, the program can not successfully
obtain the value of "Form" and
use the value of "Form" as the UserForm selected.

Can u give some advice to solve this problem?

Hi guys

I call upon the gurus again!

I have created a userform in excel, on this I have a the Calendar control (Microsoft Calendar Control 11.0).

I am having a huge problem outputting the date in the correct format on the worksheet. If the output date should be 09/12/2010 it will appear as 12/09/2010. If it is 13/12/2010 it outputs absolutely fine. It is almost as if the day number is before the month number it malfunctions?

Within the controls properties, you are able to change the date to System (English) which I did.

I have changed the formatting of the output cell in the worksheet to Custom and dd/mm/yyyy and also tried the Date formatting too. I have even customised the output in vba using the below.

Any help greatly appreciated!



Hello,

I´m trying to build an inventory form where once i click a capture button, it subtracts or adds to an inventory table. Im having trouble figuring out how to use the for function.. here`s the code and a brief explanation of where i know i`m failing but having figured out. Please pardon my writing if it sucks, I`m mexican.

Here is what i pretend to achieve with this button
1) subtract or add to an inventory list, depending if im receiving a product or releasing one..
Therefore i have ,per say ,4 comboboxes with the product listing to pick, and 4 other comboboxes on the side to select the quantity of each product i select. The first problem arises when I only select one product, the 2nd combobox product list is left empty. As you will see in my code, im always looking for the combobox list in a list i have in my worksheet and then subtracting or adding depending the case. When my code is running and i only pick 1 product rather than 2,3,4 or 5 possibilites i have on my userbox, i get an error because it tries to make an operation with no values.. I hope I explained myself ok.. Im very new with VB so sorry if i suck.

2)The 2nd part of my code tries to carry this history to another worksheet. ( was working fine but now it`s not

3)next I want to clear the user form but ,I`m not getting that, I was yesterday but don`t know what happened.

4) I`m also trying to carry the history to 2 different sheets by using a multipage in my userform. one page for arriving parts , and another for leaving parts.. any advice on how to do this?

Here`s the code.


Dim r As Range
Dim i As Range
Dim RowCount As Long

Set r = Range("A2", Range("A65536").End(xlUp))


' Aqui resto o sumo a inventario de acuerdo al 1er elemento.
For Each i In r
If i = ComboBox1.Value And OptionButton1 = True Then
i.Offset(0, 1) = i.Offset(0, 1) + CBcant1.Value
Else
If i = ComboBox1.Value And OptionButton2 = True Then
i.Offset(0, 1) = i.Offset(0, 1) - CBcant1.Value

End If
End If

Next i

' Aqui resto o sumo a inventario de acuerdo al 2do elemento.
For Each i In r
If i = ComboBox2.Value And OptionButton1 = True Then
i.Offset(0, 1) = i.Offset(0, 1) + CBcant2.Value
Else
If i = ComboBox2.Value And OptionButton2 = True Then
i.Offset(0, 1) = i.Offset(0, 1) - CBcant2.Value

End If
End If

Next i

' Aqui resto o sumo a inventario de acuerdo al 3er elemento.
For Each i In r
If i = ComboBox3.Value And OptionButton1 = True Then
i.Offset(0, 1) = i.Offset(0, 1) + CBcant3.Value
Else
If i = ComboBox3.Value And OptionButton2 = True Then
i.Offset(0, 1) = i.Offset(0, 1) - CBcant3.Value

End If
End If

Next i

' Aqui resto o sumo a inventario de acuerdo al 4to elemento.
For Each i In r
If i = ComboBox4.Value And OptionButton1 = True Then
i.Offset(0, 1) = i.Offset(0, 1) + CBcant4.Value
Else
If i = ComboBox4.Value And OptionButton2 = True Then
i.Offset(0, 1) = i.Offset(0, 1) - CBcant4.Value

End If
End If

Next i

' Aqui resto o sumo a inventario de acuerdo al 5to elemento.
For Each i In r
If i = ComboBox5.Value And OptionButton1 = True Then
i.Offset(0, 1) = i.Offset(0, 1) + CBcant5.Value
Else
If i = ComboBox5.Value And OptionButton2 = True Then
i.Offset(0, 1) = i.Offset(0, 1) - CBcant5.Value

End If
End If

Next i

' Aqui me aseguro que se llenen los campos que quiero
If Me.TextBox6.Value = "" Then
MsgBox "Favor de Llenar campo de Solicitante.", vbExclamation, "Sistema de Almacèn Rocaacero"
Me.TextBox6.SetFocus
Else
Cells(49, 5).Value = Cells(49, 5).Value + 1
Exit Sub
End If

If Me.ComboBox6.Value = "" Then
MsgBox "Favor de Llenar campo de Almacenista.", vbExclamation, "Sistema de Almacèn Rocaacero"
Me.ComboBox6.SetFocus
Exit Sub
End If
 
 
' Historial
 
RowCount = Worksheets("Hoja3").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Hoja3").Range("A1")
.Offset(RowCount, 0).Value = Me.TextBox6.Value
.Offset(RowCount, 1).Value = Me.ComboBox1.Value
.Offset(RowCount, 2).Value = Me.TextBox12.Value
If OptionButton1 = True Then
.Offset(RowCount, 3).Value = "entrada"
Else
If OptionButton2 = True Then
.Offset(RowCount, 3).Value = "salida"
End If
End If
 
If Me.CBcant2.Value = "" Then
Exit Sub
Else
RowCount = RowCount + 1
.Offset(RowCount, 0).Value = Me.TextBox6.Value
.Offset(RowCount, 1).Value = Me.ComboBox2.Value
.Offset(RowCount, 2).Value = Me.TextBox12.Value
If OptionButton1 = True Then
.Offset(RowCount, 3).Value = "entrada"
Else
If OptionButton2 = True Then
.Offset(RowCount, 3).Value = "salida"
End If
End If
 
If Me.CBcant3.Value = "" Then
Exit Sub
Else
RowCount = RowCount + 1
.Offset(RowCount, 0).Value = Me.TextBox6.Value
.Offset(RowCount, 1).Value = Me.ComboBox3.Value
.Offset(RowCount, 2).Value = Me.TextBox12.Value
If OptionButton1 = True Then
.Offset(RowCount, 3).Value = "entrada"
Else
If OptionButton2 = True Then
.Offset(RowCount, 3).Value = "salida"
End If
End If
End If
 
 
If Me.CBcant4.Value = "" Then
Exit Sub
Else
RowCount = RowCount + 1
.Offset(RowCount, 0).Value = Me.TextBox6.Value
.Offset(RowCount, 1).Value = Me.ComboBox4.Value
.Offset(RowCount, 2).Value = Me.TextBox12.Value
If OptionButton1 = True Then
.Offset(RowCount, 3).Value = "entrada"
Else
If OptionButton2 = True Then
.Offset(RowCount, 3).Value = "salida"
End If
End If
End If
 
If Me.CBcant5.Value = "" Then
Exit Sub
Else
RowCount = RowCount + 1
.Offset(RowCount, 0).Value = Me.TextBox6.Value
.Offset(RowCount, 1).Value = Me.ComboBox5.Value
.Offset(RowCount, 2).Value = Me.TextBox12.Value
If OptionButton1 = True Then
.Offset(RowCount, 3).Value = "entrada"
Else
If OptionButton2 = True Then
.Offset(RowCount, 3).Value = "salida"
End If
End If
End If
 
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
Else
If TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
End If
Next ctl
End If
End With
End Sub


Ive been assigned with a assignment for Uni to create a system in excel.

Im currently stuck on creating the user form using the submit button. Ive used a tutorial to create the form and the code, but it doesnt work i get a subscript out of range error 9)

Here is what i have
Private Sub addpatient_Click()

Dim RowCount As Long
Dim ctl As Control
' Check user input
If Me.txtfirstname.Value = "" Then
MsgBox "Please enter a First Name.", vbExclamation, "Patients "
Me.txtfirstname.SetFocus
Exit Sub
End If
If Me.txtlastname.Value = "" Then
MsgBox "Please enter a Last Name.", vbExclamation, "Patients"
Me.txtfirstname.SetFocus
Exit Sub
End If
If Me.txtaddress.Value = "" Then
MsgBox "Please enter an address.", vbExclamation, "Patients"
Me.txtfirstname.SetFocus
Exit Sub
End If
If Me.txtDOB.Value = "" Then
MsgBox "Please enter a Date.", vbExclamation, "Patients"
Me.txtfirstname.SetFocus
Exit Sub
End If

' Write data to worksheet
RowCount = Worksheets("Sheet3").Range("A7").CurrentRegion.Rows.Count
With Worksheets("Sheet3").Range("A7")
.Offset(RowCount, 0).Value = Me.txtfirstname.Value
.Offset(RowCount, 1).Value = Me.txtlastname.Value
.Offset(RowCount, 2).Value = Me.txtaddress.Value
.Offset(RowCount, 3).Value = DateValue(Me.txtDOB.Value)
.Offset(RowCount, 4).Value = Me.txtprevnotes.Value

End With
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub Any ideas, it highlights the "Sheet 3" bit but ive changed it the other name too with the same error.

I also have another error on another form saying Method or data member not found

No idea what they mean, ive searched all of the internet with no help, im hoping for some help here.

Thanks in advance

I have been diligently working on creating a userform for a week now and I have learned a great deal.. what I mostly learned was that I don’t much.. But none the less I forged ahead. I was able to accomplish a fair amount via the web not so much MS site but other searches that gave me a great base to learn from. Well I have hit my limit of learning and now I need to turn to the professionals for my last ditch effort. Here is a list of things I’d like my form to do:

1. Ability to navigate between next and previous records via buttons – this I have researched and have found some great source but my limited VB scripting has me a bit in the dark as to what variable to change.
2. Ability to word wrap automatically, for some reason I cannot get my text box to wordwrap when it reached the end of the text box itself.
3. Ability to update/modify an existing record
4. Ability to print directly from the form using a print button
5. Ability to search for a record
6. A drop down box gives me the ability to select from 3 different choices (Pass, Fail or Deferred)
That’s it I hope I am very grateful to anyone who can assist me. I am copying my VB code into the body of the post unfortunately I cannot upload my actual file from work.
       Private Sub Label1_Click()

        End Sub

        Private Sub CommandButton1_Click()

        End Sub

        Private Sub Cmd_Clear_Click()
        'Unload Me
        For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
        ElseIf TypeName(ctl) = "CheckBox" Then
        ctl.Value = False
        End If
        Next ctl
        End Sub

        Private Sub CMD_Nxt_Click()
        With Sheets("Database").Range("B7:B600")
        Debug.Print TxtAssetNo.Value
        Set c = .Find(TxtAssetNo.Value, LookIn:=xlValues)
        If Not c Is Nothing Then
        firstAddress = c.Address
        Do
        currow = c.Row
        Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
        End With
        Debug.Print currow
        currow = currow + 1

        TxtAssetNo = Range("B" & currow).Value
        TxtboxSupp = Range("C" & currow).Value
        TxtboxInvNo = Range("D" & currow).Value
        End Sub





        Private Sub CMD_Prev_Click()

        With Sheets("One_Review_Data").Range("A1:A600")
        Debug.Print Txt_WON.Value
        Set c = .Find(Me.Txt_WON.Value, LookIn:=xlValues)
        If Not c Is Nothing Then
        ' firstAddress = c.Address
        ' Do
        currow = c.Row
        Debug.Print currow
        ' Set c = .FindNext(c)
        ' Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
        currow = currow - 7 ' this will show prevous cell

        Txt_WON.Value
        End With
        'etc....
        End Sub

        Private Sub CMD_submit_Click()
        Dim ctl As Control

        Dim rowcount As Long
        rowcount = Worksheets("One_Review_Data").Range("A1").CurrentRegion.Rows.Count
        With Worksheets("One_Review_Data").Range("A1")
        .Offset(rowcount, 0).Value = Me.Txt_WON.Value
        .Offset(rowcount, 1).Value = Me.Txt_WO.Value
        .Offset(rowcount, 2).Value = Me.Txt_WOM.Value
        .Offset(rowcount, 3).Value = Me.Txt_WOG.Value
        .Offset(rowcount, 4).Value = Me.Txt_WOMa.Value
        .Offset(rowcount, 5).Value = Me.Txt_WOIP.Value
        .Offset(rowcount, 6).Value = Me.Txt_WOE.Value
        .Offset(rowcount, 7).Value = Me.Txt_WOEE.Value
        .Offset(rowcount, 8).Value = Me.Txt_WOT.Value
        End With

        If Me.Txt_WON.Value = "" Then
        MsgBox "Please enter workorder number first.", vbExclamation, "Work Order Number"
        Me.Txt_WON.SetFocus
        Exit Sub
        End If
        If Not IsNumeric(Me.Txt_WON.Value) Then
        MsgBox "This box must contain number.", vbExclamation, "Work Order Number"
        Me.Txt_WON.SetFocus
        Exit Sub
        End If

        If Me.Txt_WOE.Value = "" Then
        MsgBox "Please enter Engineers full name.", vbExclamation, "Work Order Number"
        Me.Txt_WOE.SetFocus
        Exit Sub
        End If
        If Me.Txt_WOT.Value = "" Then
        MsgBox "Please enter Work Order Title.", vbExclamation, "Work Order Number"
        Me.Txt_WOT.SetFocus
        Exit Sub
        End If
        If Me.Txt_WOEE.Value = "" Then
        MsgBox "Please enter email.", vbExclamation, "Work Order Number"
        Me.Txt_WOEE.SetFocus
        Exit Sub
        End If
        For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
        ElseIf TypeName(ctl) = "CheckBox" Then
        ctl.Value = False
        End If
        Next ctl
        End Sub

        Function ValidateEmail(ByVal sEmail As String) As Boolean

        Dim oRegularExpression As RegExp

        ' Sets the regular expression object
        Set oRegularExpression = New RegExp

        With oRegularExpression
        ' Sets the regular expression pattern
        .Pattern = "w+([-+.']w+)*@w+([-.]w+)*.w+([-.]w+)*"

        ' Ignores case
        .IgnoreCase = True

        ' Test email string
        ValidateEmail = .Test(sEmail)
        End With

        End Function


        Private Sub Txt_WON_Change()

        End Sub

        Private Sub UserForm_Click()
        Me.PrintForm
        End Sub
Thank you,

~r

I have been diligently working on creating a userform for a week now and I have learned a great deal.. what I mostly learned was that I don’t much.. But none the less I forged ahead. I was able to accomplish a fair amount via the web not so much MS site but other searches that gave me a great base to learn from. Well I have hit my limit of learning and now I need to turn to the professionals for my last ditch effort. Here is a list of things I’d like my form to do:
1. Ability to navigate between next and previous records via buttons – this I have researched and have found some great source but my limited VB scripting has me a bit in the dark as to what variable to change.
2. Ability to word wrap automatically, for some reason I cannot get my text box to wordwrap when it reached the end of the text box itself.
3. Ability to update/modify an existing record
4. Ability to print directly from the form using a print button
5. Ability to search for a record
6. A drop down box gives me the ability to select from 3 different choices (Pass, Fail or Deferred)
That’s it I hope I am very grateful to anyone who can assist me. I am copying my VB code into the body of the post unfortunately I cannot upload my actual file from work.

Private Sub Label1_Click()

End Sub

Private Sub CommandButton1_Click()

End Sub

Private Sub Cmd_Clear_Click()
'Unload Me
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

Private Sub CMD_Nxt_Click()
With Sheets("Database").Range("B7:B600")
Debug.Print TxtAssetNo.Value
Set c = .Find(TxtAssetNo.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
currow = c.Row
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Debug.Print currow
currow = currow + 1

TxtAssetNo = Range("B" & currow).Value
TxtboxSupp = Range("C" & currow).Value
TxtboxInvNo = Range("D" & currow).Value
End Sub

Private Sub CMD_Prev_Click()

With Sheets("One_Review_Data").Range("A1:A600")
Debug.Print Txt_WON.Value
Set c = .Find(Me.Txt_WON.Value, LookIn:=xlValues)
If Not c Is Nothing Then
' firstAddress = c.Address
' Do
currow = c.Row
Debug.Print currow
' Set c = .FindNext(c)
' Loop While Not c Is Nothing And c.Address <> firstAddress
End If
currow = currow - 7 ' this will show prevous cell

Txt_WON.Value
End With
'etc....
End Sub

Private Sub CMD_submit_Click()
Dim ctl As Control

Dim rowcount As Long
rowcount = Worksheets("One_Review_Data").Range("A1").CurrentRegion.Rows.Count
With Worksheets("One_Review_Data").Range("A1")
.Offset(rowcount, 0).Value = Me.Txt_WON.Value
.Offset(rowcount, 1).Value = Me.Txt_WO.Value
.Offset(rowcount, 2).Value = Me.Txt_WOM.Value
.Offset(rowcount, 3).Value = Me.Txt_WOG.Value
.Offset(rowcount, 4).Value = Me.Txt_WOMa.Value
.Offset(rowcount, 5).Value = Me.Txt_WOIP.Value
.Offset(rowcount, 6).Value = Me.Txt_WOE.Value
.Offset(rowcount, 7).Value = Me.Txt_WOEE.Value
.Offset(rowcount, 8).Value = Me.Txt_WOT.Value
End With

If Me.Txt_WON.Value = "" Then
MsgBox "Please enter workorder number first.", vbExclamation, "Work Order Number"
Me.Txt_WON.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.Txt_WON.Value) Then
MsgBox "This box must contain number.", vbExclamation, "Work Order Number"
Me.Txt_WON.SetFocus
Exit Sub
End If

If Me.Txt_WOE.Value = "" Then
MsgBox "Please enter Engineers full name.", vbExclamation, "Work Order Number"
Me.Txt_WOE.SetFocus
Exit Sub
End If
If Me.Txt_WOT.Value = "" Then
MsgBox "Please enter Work Order Title.", vbExclamation, "Work Order Number"
Me.Txt_WOT.SetFocus
Exit Sub
End If
If Me.Txt_WOEE.Value = "" Then
MsgBox "Please enter email.", vbExclamation, "Work Order Number"
Me.Txt_WOEE.SetFocus
Exit Sub
End If
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub

Function ValidateEmail(ByVal sEmail As String) As Boolean

Dim oRegularExpression As RegExp

' Sets the regular expression object
Set oRegularExpression = New RegExp

With oRegularExpression
' Sets the regular expression pattern
.Pattern = "w+([-+.']w+)*@w+([-.]w+)*.w+([-.]w+)*"

' Ignores case
.IgnoreCase = True

' Test email string
ValidateEmail = .Test(sEmail)
End With

End Function

Private Sub Txt_WON_Change()

End Sub

Private Sub UserForm_Click()
Me.PrintForm
End Sub

Thank you,

~r

I have a userform that we are using to control our inventory. Users put in an asset "number" (combination of numbers and letters) and if it finds the asset already in the sheet it populates other fields with the information and they can make their changes. If it doesn't find the asset at all it creates a new entry for it. Since we have a lot of assets it looks for an exact match.

The problem that I have run into is if they put in a space(s) or tab(s) that it doesn't recognize it as a match, so after they fill out the form and hit submit it creates a new entry and our inventory then gets duplicates.

Is there I way I can prevent them from putting tabs or spaces in the field all together or remove them when they exit the field or enter a new one?

Hi

I've added code like below in order to control inputs from users.
It works fine. But i have the following problems:

1) In Denmark where i come from we use , instead of . in numbers and this is
working in the sheets,
but when i load a value like 5,95 from a sheet it turns into 5.95,
making my formulas go crazy.
How do i control this ? I don't see a property like "Format" on a
textbox.

2) How do i control events on controls add by code.
On Exit from a textbox i need to do some calulation to keep the user
updates on price.

3) Any suggestions on how to write back the changes the user made.

Regards,
Claus

CODE:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Dim mycmd As Control, KontrolNavn As String, x As Integer
UserForm2.Controls.Clear

x = 0
While Cells(Target.Row + x + 1, 5).Value <> ""
KontrolNavn = "Label" & Str(x + 1)
Set mycmd = UserForm2.Controls.Add("Forms.Label.1", KontrolNavn,
Visible)
With mycmd
.Left = 10
.Top = 10 + x * 20
.Caption = Str(x + 1) & ". " & Cells(Target.Row + x + 1, 3).Value
.Width = 150
End With
Select Case Cells(Target.Row + x + 1, 4).Value
Case "Tekst", "Tal"
KontrolNavn = "Textbox" & x
Set mycmd = UserForm2.Controls.Add("Forms.Textbox.1", KontrolNavn,
Visible)
With mycmd
.Left = 170
.Top = 5 + x * 20
.Width = 150
.Value = Cells(Target.Row + x + 1, Target.Column).Value
End With
Case "Etiket"
KontrolNavn = "Label" & x
Set mycmd = UserForm2.Controls.Add("Forms.Label.1", KontrolNavn,
Visible)
With mycmd
.Left = 180
.Top = 10 + x * 20
.Width = 150
.Caption = Cells(Target.Row + x + 1, Target.Column).Value
End With
End Select
If InStr(1, Cells(Target.Row + x + 1, 4).Value, ":") > 0 Then
KontrolNavn = "Combobox" & x
Set mycmd = UserForm2.Controls.Add("Forms.combobox.1", KontrolNavn,
Visible)
With mycmd
.Left = 170
.Top = 5 + x * 20
.Width = 150
.RowSource = Cells(Target.Row + x + 1, 4).Value
.Value = Cells(Target.Row + x + 1, Target.Column).Value
End With
End If

x = x + 1
Cells(Target.Row + x, 5).Select
Wend

UserForm2.Show
End Sub

I have a large userform which records various pieces of information about
projects. Each project has a number of uniyts on it, each of which can have
different parameters.

To stop the userform becoming too large with too many options on it, i
would like to be able to click a command button which would then direct the
selections to a new sheet. All the cell references would stay the same. In
short i am looking to 're-use' the userform.

The issue i have is that i set the control source within the properties
window for each textbox, dropdown etc, but to achieve what i want, i would
need them to all update at once with the new sheet name before the cell
reference.

Incidentally, is this something i should really be doing in Access?

Any help is greatly appreciated
--
Richard

I've wasted the last couple of hours trying to figure this out. I want to get the text of the selected value in a Form Controls listbox which is in a worksheet. In Excel 2003 I could just type:

Sheets(1).Range("A1").Value = myListBox.Text

The best I've been able to do in Excel 2007 is to get the index and that uses

Sheets(1).Range("A1").Value = Sheets(1).ListBoxes(1).ListIndex

The command works fine if I put the listbox in a userform instead, but I need to have it on a worksheet. I also don't understand why I have to use ListBoxes instead of whatever I name the listbox. Has MS changed something in 2007 or have I just done something wrong?

Hello All,

I am trying to enter data onto a spreadsheet using a UserForm with controls on a multipage control. My combo boxes work when placed directly on the UserForm, but do not work when placed on a Multipage on the UserForm.

My code for the latter is:

Private Sub OKButton_Click()
ActiveWorkbook.Sheets("Sheet1").Activate
Range("E8").Select

ActiveCell.Offset(0, 0) = MultiPage1.Page1.ComboBox1.Value
ActiveCell.Offset(1, 0) = MultiPage1.Page1.ComboBox2.Value
ActiveCell.Offset(2, 0) = MultiPage1.Page1.ComboBox3.Value
ActiveCell.Offset(3, 0) = MultiPage1.Page1.ComboBox4.Value
ActiveCell.Offset(4, 0) = MultiPage1.Page1.ComboBox5.Value
MultiPage1.Page1.ComboBox1.Text = ""
MultiPage1.Page1.ComboBox2.Text = ""
MultiPage1.Page1.ComboBox3.Text = ""
MultiPage1.Page1.ComboBox4.Text = ""
MultiPage1.Page1.ComboBox5.Text = ""
MultiPage1.Page1.ComboBox1.SetFocus
End Sub

the above does not work when combo boxes are on a Multipage.

Private Sub OKButton_Click()
ActiveWorkbook.Sheets("Sheet1").Activate
Range("E8").Select

ActiveCell.Offset(0, 0) = ComboBox1.Value
ActiveCell.Offset(1, 0) = ComboBox2.Value
ActiveCell.Offset(2, 0) = ComboBox3.Value
ActiveCell.Offset(3, 0) = ComboBox4.Value
ActiveCell.Offset(4, 0) = ComboBox5.Value
ComboBox1.Text = ""
ComboBox2.Text = ""
ComboBox3.Text = ""
ComboBox4.Text = ""
ComboBox5.Text = ""
ComboBox1.SetFocus
End Sub

The above DOES] work when placed directly on the UserForm.

Any help would be appreciated as this is the first time that I have tried to use Multipage and my project requires several of them.

Thanks!

Hi Everyone,

First off I am new to VB for excel, most everything I need to do is just done with excel.

I am running into a problem. I am trying to create a Userform to make User input easier because my spreadsheet is so long and intensive.

I created Userform1 and My Textbox1 pulls a Report # from the Daily Mud Report Tab. I need all my textboxes to Use this number to lookup the Report # in the Mud Checks Tab Column A, then populate the other text boxes accordingly. The problem I think I am facing is that the Textbox 1 and textbox 2 are outside of the multipage control. I need these outside of the multipage control so users do not have to reenter a report number and date for all 4 of the multipage tabs. Each Multipage tab is supposed to be the same report number, but pulling data from different columns since we have 4 reports in any one day. Mud Check 2 In pulls data from "Mud Checks"range A:AL, then the next mud check 2 in further down the spreadsheet.

I thought using a controlsource property would work at first but it is only good for one cell or range. Then I tried writing the code out in VB

Similiar to this. But it doesn't pull into the multipage form.

What would be the best way to have these pull from a VLookup and still be able to update the corresponding cells in the
worksheet on text change or button click?

I have a sheet which runs a video in windows media player. I would like to index certain times on a video by pausing the video and allowing these times to be input into text boxes in a userform. The reason I am using a userform is that it gives more control over the data input and allows to tuck away an ugly part of the presentation. Right now, I use another program to do the video indexing. Is there any way that I can write VBA code to allow back and forth between the excel sheet video and the userform?

Lucas

---- ANOTHER EDIT -----

I'll try to sum up the whole thing to you guys, than maybe someone can help me.

Although the
 and the 
	

	
 works on this listbox, depending on which list item I'm trying to select It just won't work.

Whenever I try to use variables insted of the "1", it won't work, mostly the times or situations.

If I try to run any operation with the variable it just won't work (Like A UserForm.ListBox.ListCount - 1, wouldn't work)

Whenever I try to select the last item it won't work

-----------------------------------

Hi Guys..

I'm having this unusual trouble with the listindex property of a listbox on an userform.

The code below search for a range and refreshes the listbox with the range values.
Sub Form_Esquadrias_Atualiza(IndexCache As Integer)

    Dim Aux As Worksheet
    Dim Cont1 As Integer
    Dim ListaRange As Range
    Dim ActSheet As Worksheet
    Dim Lista As MSForms.ListBox
    
    Set Aux = Sheets("AUXILIAR")
    Set ActSheet = ActiveSheet

    Call Form_Calcular_Esquadrias

    Cont1 = 1
    Do While Aux.Cells(Cont1, 11).Value <> Empty
        Cont1 = Cont1 + 1
    Loop
    Cont1 = Cont1 - 1
    If Cont1 = 0 Then Form_Esquadrias.Esq_Lista.Clear
    
    If Cont1 > 0 Then
        Application.ScreenUpdating = False
        Aux.Visible = True
        Aux.Select
        Set ListaRange = Aux.Range(Cells(1, 11), Cells(Cont1, 15))
        Set Lista = Form_Esquadrias.Esq_Lista
        With Lista
            .List = ListaRange.Cells.Value
            .ColumnCount = 5
            .ColumnWidths = "28;40;40;40;30"
        End With
        ActSheet.Select
        Aux.Visible = False
        Application.ScreenUpdating = True
    End If
    
    Lista.ListIndex = IndexCache
    
End Sub
The sub routine that I'm fireing in the midle of the code above can be seen here:

Sub
Form_Calcular_Esquadrias()

    Dim Aux As Worksheet
    Dim Lanca As Worksheet
    Dim Resumo As Worksheet
    Dim ChkReset As Integer
    Dim Cont1 As Integer
    Dim Ini1 As Integer
    Dim Fim1 As Integer

    Set Aux = Sheets("AUXILIAR")
    Set Lanca = Sheets("Lançamento")
    Set Resumo = Sheets("Resumo")
    
    Cont1 = 1
    Do While Lanca.Cells(Cont1, 2).Value <> "#2"
        If Lanca.Cells(Cont1, 2).Value = "#1" Then Ini1 = Cont1 + 2
        If Lanca.Cells(Cont1 + 1, 2).Value = "#2" Then Fim1 = Cont1
        Cont1 = Cont1 + 1
    Loop
    
    Cont1 = 1
    Do While Resumo.Cells(Cont1, 2).Value <> "#2"
        If Resumo.Cells(Cont1, 2).Value = "#1" Then Ini2 = Cont1 + 2
        If Resumo.Cells(Cont1 + 1, 2).Value = "#2" Then Fim2 = Cont1
        Cont1 = Cont1 + 1
    Loop
    
    Aux.Columns(15).ClearContents
    
    Cont3 = 1
    Do While Aux.Cells(Cont3, 11).Value <> Empty
        For Cont1 = Ini1 To Fim1
            Col = 13
            Do While Col <> 0
                If Aux.Cells(Cont3, 11).Value = Lanca.Cells(Cont1, Col) Then
                    QtdCache = 0
                    For Cont2 = Ini2 To Fim2
                        If Lanca.Cells(Cont1, 3).Value = Resumo.Cells(Cont2, 3).Value Then
                            QtdCache = Resumo.Cells(Cont2, 5).Value
                            Cont2 = Fim2 + 1
                        End If
                    Next Cont2
                    Aux.Cells(Cont3, 15) = Aux.Cells(Cont3, 15).Value + Lanca.Cells(Cont1, Col + 1).Value * QtdCache
                End If
                If Col = 17 Then Col = 0
                If Col = 13 Then Col = 17
            Loop
        Next Cont1
        Cont3 = Cont3 + 1
    Loop

End Sub
On my form, I got 2 command buttons which I use to change the position of an Item on the predeterminated range and, I fire the listbox refreshing code.

One of the buttons, sends the selected listindex up and the other one sends it down.

the "send up" code can be seen here:

Private Sub Sobe_Click()

    Dim Aux As Worksheet
    Dim Cache As String
    Dim LiCache As Integer
    
    Set Aux = Sheets("AUXILIAR")

    If Form_Esquadrias.Esq_Lista.ListIndex < 1 Then Exit Sub
    LiCache = Esq_Lista.ListIndex - 1
    For Col = 11 To 13
        Cache = Aux.Cells(Form_Esquadrias.Esq_Lista.ListIndex + 1, Col).Value
        Aux.Cells(Form_Esquadrias.Esq_Lista.ListIndex + 1, Col) = Aux.Cells(Form_Esquadrias.Esq_Lista.ListIndex, Col).Value
        Aux.Cells(Form_Esquadrias.Esq_Lista.ListIndex, Col) = Cache
    Next Col

    Call Form_Esquadrias_Atualiza(LiCache)
    
End Sub

The thing is, as you can see, the LiCache variable, which can be seen in the last line of the code above, is the ListIndex of the selected item after I changed its position up (ex.: ListIndex - 1), and, in the first code I wrapped, I try to keep the previously selected item still selected.

I have oftenly used this kind of control on my listboxes, but this time IT JUST DOESN'T WORK.

I have tested it so many times and it even stores the right value in the LiCache variable and also changes the listbox.listindex to the desired value, but it doesn't select the item.

I hope I made myself clear.

Thanks for your attention.

Att. Daniel

--------- EDIT -----------
The UserForm can be seen on the attached picture. The two buttons I was Talking about are the / and / last two.

Sorry Roy UK, thanks for correcting me.

Hi all- I'm sorry I know I've posted it twice, I'm really under pressure and need to fix this fast!

I'm creating what should be a fairly simple file. I have a userform that people fill in then they hit save and it writes to the worksheet "DATABASE". In the form you enter, amongst other info, the resource used and length of time. I then have a cell formula using VLOOKUP that calculates the cost based on the resource and time and another cell which adds this figure to the cost of the stock.

However, because I copied these two formulas into their columns when I press save the vb code i have puts the data in the next empty line. So either I have to find a way to write the forumlas in the code or get the form to save to the sheet differently.

Here's the code with the irrelevent stuff taken out.

Private Sub cmdSubmit_Click()

Dim RowCount As Long
Dim ctl As Control

' Check user input...

' Message to check first
Dim confirm As VbMsgBoxResult
     
    confirm = MsgBox("Are you sure you want to submit?", _
    vbYesNo, "STOP: Submit Data")
    If confirm = 6 Then

' Write data to worksheet

RowCount = Worksheets("Database").Range("A1").CurrentRegion.Rows.Count

With Worksheets("Database").Range("A1")
.Offset(RowCount, 0).Value = "P1088 (Bris)"
.Offset(RowCount, 1).Value = "80192D"
.Offset(RowCount, 2).Value = Me.txtWBS.Value
.Offset(RowCount, 4).Value = Me.txtDoj.Value
.Offset(RowCount, 5).Value = Me.txtAT.Value
.Offset(RowCount, 7).Value = Me.txtSC.Value
.Offset(RowCount, 8).Value = Me.txtJobdesc.Value
.Offset(RowCount, 9).Value = Me.cmbAct.Value
.Offset(RowCount, 10).Value = Me.txtWk.Value
.Offset(RowCount, 11).Value = Me.cmbPN.Value
.Offset(RowCount, 12).Value = Me.cmbFac.Value
.Offset(RowCount, 13).Value = Me.txtBT.Value
.Offset(RowCount, 14).Value = Me.txtPC.Value
.Offset(RowCount, 15).Value = Me.txtStock.Value
.Offset(RowCount, 16).Value = Me.txtMC.Value

End With
' Clear form after submitting...
As you can see numbers 3 and 6 are missing, these (columns C and F) are the ones with the formula already in. I tried switching it round so these were the first 2 columns and typing Range C1 at the start but that just errored and debugged highlighting that line of code.
The formula in column F is =IF(ISERROR(VLOOKUP(S13,BACKGROUND!J:M,4,FALSE)=TRUE),"",VLOOKUP(S13,BACKGROUND!J:M,4,FALSE)) the forumla in C is =IF(I13="",0,SUM((H13*I13)+J13))
Any ideas? I'm really stuck on this one and i need to complete the whole thing and enter almost 100 forms on it by tues evening! eek!
many thanks,
z