Free Microsoft Excel 2013 Quick Reference

VBA userform will not auto-fill

The code below allows me to enter data into a form by double clicking on a cell in column A. When I enter my data into the form, I click ‚Äúnew entry‚ÄĚ, the data is entered into the last unfilled row. What I like to accomplish in addition to the above code is take existing data from my worksheet and have it auto-fill the form so I can make changes to that existing data and than put the corrected data back into the same row the data came from.

Double-Click Code for Cells in Column A:

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
If Not (Nothing Is Application.Intersect(Target, Range("A:A"))) Then
Cancel = True
frmLogbook.Show
End If
End Sub
 
 
"New Entry" button Code:
 
Private Sub CommandButton1_Click()
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Logbook")
 
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
 
'check for a part number
If Trim(Me.txtDATE.Value) = "" Then
Me.txtDATE.SetFocus
MsgBox "Please Make an Entry"
Exit Sub
End If
 
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtDATE.Value
ws.Cells(iRow, 2).Value = Me.txtTYPE.Value
ws.Cells(iRow, 3).Value = Me.txtIDENT.Value
ws.Cells(iRow, 4).Value = Me.txtROUTE.Value
ws.Cells(iRow, 5).Value = Me.txtTOTAL.Value
ws.Cells(iRow, 6).Value = Me.txtSEL.Value
ws.Cells(iRow, 7).Value = Me.txtSES.Value
ws.Cells(iRow, 8).Value = Me.txtMEL.Value
ws.Cells(iRow, 9).Value = Me.txtOPT1.Value
ws.Cells(iRow, 10).Value = Me.txtOPT2.Value
ws.Cells(iRow, 11).Value = Me.txtOPT3.Value
ws.Cells(iRow, 12).Value = Me.txtOPT4.Value
ws.Cells(iRow, 13).Value = Me.txtOPT5.Value
ws.Cells(iRow, 24).Value = Me.txtPIC.Value
ws.Cells(iRow, 25).Value = Me.txtSIC.Value
ws.Cells(iRow, 27).Value = Me.txtCFI.Value
ws.Cells(iRow, 23).Value = Me.txtSOLO.Value
ws.Cells(iRow, 26).Value = Me.txtDUAL.Value
ws.Cells(iRow, 22).Value = Me.txtXCTRY.Value
ws.Cells(iRow, 21).Value = Me.txtFLTSIM.Value
ws.Cells(iRow, 17).Value = Me.txtIMC.Value
ws.Cells(iRow, 18).Value = Me.txtSIM.Value
ws.Cells(iRow, 16).Value = Me.txtNIGHT.Value
ws.Cells(iRow, 19).Value = Me.txtAPPCH.Value
ws.Cells(iRow, 20).Value = Me.APPCHTYPE.Value
ws.Cells(iRow, 14).Value = Me.txtDAY.Value
ws.Cells(iRow, 15).Value = Me.txtNIGHTLDG.Value
ws.Cells(iRow, 28).Value = Me.txtREMARKS.Value
 
'clear the data
Me.txtDATE.Value = ""
Me.txtTYPE.Value = ""
Me.txtIDENT.Value = ""
Me.txtROUTE.Value = ""
Me.txtTOTAL.Value = ""
Me.txtSEL.Value = ""
Me.txtSES.Value = ""
Me.txtMEL.Value = ""
Me.txtOPT1.Value = ""
Me.txtOPT2.Value = ""
Me.txtOPT3.Value = ""
Me.txtOPT4.Value = ""
Me.txtOPT5.Value = ""
Me.txtPIC.Value = ""
Me.txtSIC.Value = ""
Me.txtCFI.Value = ""
Me.txtSOLO.Value = ""
Me.txtDUAL.Value = ""
Me.txtXCTRY.Value = ""
Me.txtFLTSIM.Value = ""
Me.txtSIM.Value = ""
Me.txtNIGHT.Value = ""
Me.txtAPPCH.Value = ""
Me.APPCHTYPE.Value = ""
Me.txtDAY.Value = ""
Me.txtNIGHTLDG.Value = ""
Me.txtREMARKS.Value = ""
Me.txtDATE.SetFocus
 
Unload Me
End Sub

What is currently showing in my VBA project (ATL-F11) is frmLogbook and Module 1. Module 1 has the following code below…

Code:
Sub Rectangle3_Click()
ShowForm.Show False
End Sub
'------------------------------------------------------------------------------------------------------------
Sub ShowForm()
With frmLogbook.APPCHTYPE
.RowSource = ""
.AddItem "ILS"
.AddItem "LOC"
.AddItem "VOR"
.AddItem "NDB"
.AddItem "LDA"
.AddItem "BC-LOC"
.AddItem "RNAV"
.AddItem "GPS"
.AddItem "MLS"
End With
frmLogbook.Show
End Sub
I was collaborating with a member who was helping me with this problem, but I was not successful in implementing the code. The code below is supposed to accomplish what I’m trying to achieve, but it does not take the existing data and autofill the form. I was wondering if anyone else could help me with this.

Code:
Option Explicit
Dim myCells As Range
 
Private Sub UserForm_Initialize()
With ActiveCell
If .Value = vbNullString Then
With .Parent
Set myCells = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
Else
Set myCells = ActiveCell.EntireRow.Range("A1")
End If
Set myCells = myCells.Resize(1, 28)
End With
Call FromSheetToUserform
Me.txtTYPE.SetFocus
End Sub
 
Sub FromSheetToUserform()
Dim myData As Variant
 
myData = Application.Transpose(Application.Transpose(myCells.Value))
 
With Me
.txtDATE.Text = CStr(myData(1))
.txtTYPE.Text = CStr(myData(2))
Rem code
.txtREMARKS.Text = CStr(myData(28))
End With
End Sub
 
Private Sub CommandButton1_Click()
With Me
If .txtDATE.Text = vbNullString Then
MsgBox "Please enter a DATE"
.txtDATE.SetFocus
Else
myCells.Range("A1").Select
Call FromUFormToSheet
Call clearTextBoxes: Rem not needed if form is to be unloaded
Unload Me
End If
End With
End Sub
 
Sub FromUFormToSheet()
Dim myData() As String
ReDim myData(1 To myCells.Cells.Count)
 
With Me
myData(1) = .txtDATE.Text
myData(2) = .txtTYPE.Text
Rem code
myData(28) = .txtREMARKS
End With
 
myCells.Value = myData
End Sub
 
Sub clearTextBoxes()
Dim xControl As Object
For Each xControl In Me.Controls
If TypeName(xControl) = "TextBox" Then
xControl.Text = vbNullString
End If
Next xControl
End Sub
I hoping someone can help me solve this. I really, really, really, would like to have this feature in my project


Post your answer or comment

comments powered by Disqus
How do I auto-fill dates that are not consecutive across a row? Here's
the situation:

I've created an attendance report for a number of instructors. Near
the top, the instructor enters the dates the class meets for the
semester.

Presuming it meets Mondays and Wednesdays starting on 1/2/06 (Monday),
how can I use Excel (VBA or otherwise) to "auto-fill" the dates,
provided the start and end dates are already entered (either in the
worksheet or in a user form)?

So, in this example, if the start date is 1/2 and the end date is 4/26,
I want the attendance report to auto-fill (starting in J10) 1/2, 1/4,
1/9, 1/11, 1/16 ... 4/26. To slightly add to my problem, the classes
may meet Mon/Wed, Tue/Thu, Mon/Wed/Fri, Mon/Thu, etc.

Any help would be appreciated. I'm new to VBA, but I've been delving
through this newsgroup for about 2 months and a book by John
Walkenbach, both of which have been very helpful in writing a number of
procedures. I'm using Office 2003. Thanks!!

Hi Guys,

I need to produce a grid displaying data pulled from local references within the workbook but Excel will not auto fill the references in the right way.

i need

='Account (1)'!$C$5
='Account (2)'!$C$5
='Account (3)'!$C$5

down to

='Account (498)'!$C$5
='Account (499)'!$C$5
='Account (500)'!$C$5

is there a way to imput these references using VBA script?

thanks in advance for any help

F

Excel VBA spreadsheet runs fine on other PCs but will not run on one

--Excel 2000 (all machines)

ON Bad PC:
--Controls can not be activated
--Contols show no properties
--VBA editor and code can be seen
--When adding a new control error message "cannot insert object" appears
--no other programs are used (like C++..)
--changing security had no affect

Thanks,
Tim

Hi

I'm having trouble with a file I made that uses combo boxes in a user form for entering data, and selecting the data if it already exists.
The combo boxes will not auto fill on either version of excel I have (2001, 2011) however when I use the same file on a PC they work fine.
Is there something more required than just activating it in the combo box properties window for the mac?

Any help is much appreciated.
Kiwi

I have a program that I got on this forum about a year ago. It has been working great for a program that I needed it for. I am now needing it for a different type program. I tried duplicating the code and it will not work. When I input the student's ID and click on search student it tells me that, that ID does not exist. I know it does because I am looking right at it. It works on my other program. It should fill in the student's LastName, FirsName and Grade Level. I have the example program attached. Thank you.

I have a userform that pops up and stays up while other macros run. I would like to have the text on the userform change as it progresses through the macros. Why is it that the attached userform will not update the text if you run straight through it? BUT, if I F8 my way through it, the userform changes text like i want it to.

I have the 2-second pause in there just for this example so it will not just run through it quickly.

Here is the code if you dont want to view the file:

Private Sub UserForm_Activate()

    On Error GoTo ErrHandler
    Please_Wait_Label.Caption = "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "Please wait.." & vbNewLine & _
                "" & vbNewLine & _
                "Your contract is being generated." & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "This process may take as long as (4-8) minutes to complete."
                
'I have macros here

Application.Wait (Now + TimeValue("0:00:02"))   'Added for this test
Application.ScreenUpdating = True
Application.EnableEvents = True
    Please_Wait_Label.Caption = "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "Please wait.." & vbNewLine & _
                "" & vbNewLine & _
                "Contract Status:" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "Performing text replacement for your Contract."
                
Application.ScreenUpdating = False
Application.EnableEvents = False

'I have macros here

Application.Wait (Now + TimeValue("0:00:02"))   'Added for this test
Application.EnableEvents = True
Application.ScreenUpdating = True
    Please_Wait_Label.Caption = "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "Please wait.." & vbNewLine & _
                "" & vbNewLine & _
                "Contract Status:" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "Removing unecessary Crane Sheets from your Contract."
                
Application.ScreenUpdating = False
Application.EnableEvents = False

'I have macros here

Application.Wait (Now + TimeValue("0:00:02"))   'Added for this test
Application.EnableEvents = True
Application.ScreenUpdating = True
    Please_Wait_Label.Caption = "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "Please wait.." & vbNewLine & _
                "" & vbNewLine & _
                "Contract Status:" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "Applying your Acessories to your Contract."
                
Application.ScreenUpdating = False
Application.EnableEvents = False

'I have macros here

Application.Wait (Now + TimeValue("0:00:02"))   'Added for this test
Application.EnableEvents = True
Application.ScreenUpdating = True
    Please_Wait_Label.Caption = "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "Please wait.." & vbNewLine & _
                "" & vbNewLine & _
                "Contract Status:" & vbNewLine & _
                "" & vbNewLine & _
                "" & vbNewLine & _
                "Finalizing your Contract."
                
Application.ScreenUpdating = False
Application.EnableEvents = False


    Unload Me       'Unload the UserForm
    
    Exit Sub
ErrHandler:
'I have macros here
End Sub


hi can u help me in doing my formula on alpha numeric. see attached file. dont have any problem in numeric series i can do this by draging but when the last character is text like. 123abc the excel can not auto fill it. please help me

ygops

Hi!

Im trying to set up an excel spread sheet with some drop down tabs. I want
to be able to select a certain "item" from the drop down tab which will then
auto fill other cells to give me certain figures.

This table is for costing and ordering hardware for cabinets. If i select a
certain cabinet, i want the next cell to then have a drop down list of the
sizes available in that cabinet, which then gives me a price in the next cell
for that cabinet and its size. Also then i want the number of certain items
needed for that cabinet to auto fill in the cells after that.

So i should be able to select the cabinet, then a size that cabinet is
avialable in. Then look at the price and what hardware is needed for that
cabinet

If this has been covered somewhere please point me in the right direction!

Thanks
Luke

I am wondering how to link a spreadsheet to a table that will allow
information from a a table to be autofilled into specific cells on another
sheet as I type similar information that matches certain cells. example
below.

Table
part number qty
xxx-01 10

Spreadsheet
when I type xxx-01 / 10 will be auto filled in.

Thank you for any help.

I have this formula to sum the cells with certain font colors that will not caluclate by itself unless under very few conditions:

=CountFontColor(A3:A13,-4105)-COUNTIF(A3:A13,"")

My problem is that I am creating this spreadsheet to be as easy to use as possible (users have little to no excel knowledge) and the cells that need to be counted are set to change color based on a conditional format - this conditional format is based on a range of cells in column B - which are drop-downs (to make it fool-proof for the user)

So, the problem is that column A is pre-populated with names, column B has a drop down....if a certain 'absence code' is selected in column B, the corresponding name in column A will change color....but the total of my formula =CountFontColor(A3:A13,-4105)-COUNTIF(A3:A13,"") remains the same until I click on the cell and hit 'enter'. I can't have these users doing that so how do I get it to calculate by itself?

Thanks

Hi,

I have an invoice that I use and I want to make it so that when I type in the name of an item that it would first auto-fill the name of the design after I start typing a few letters and then auto fill the price of the item.

I have looked at a few examples in this forum so I have created a "sheet2" and made two colums "Design Name" and "Price" but I have no idea what to do to "link" this sheet up with the invoice. Because when I type in the name of the design in the invoice, the price is not auto-filling.

I'm stumped. I google EVERYTHING and I can't find the answer to this anywhere. The closest I've found is on this forum but after following the
instructions on a couple of posts I'm still unsuccessful. and its really bugging me and hope someone can provide me with some assistance.

I've attached a copy of my invoice (of course I have omitted the private info)

I am needing some help on a coding issue in an Excel
Macro. I want the macro look for a value in a cell and
when it finds that value, I want it to delete that row.
Is this possible?

Also, are there any tips out there on how to code the
macro to start looking from the bottom up, instead of the
top down? I want to try to incorporate the macro to Auto
fill, but my data varies each time I import information.
If I auto fill the way I have it now, it will not auto
fill if I have more records.

Any help would be appreciated. Thanks and have a great
weekend!-----Julie

Hello everyone. New to VBA so pardon if there is a similar post with the answer that I was unable to understand. I searched and did not find anything to help me or that I understood would help anyway. I used HTML Maker to show my sheet below. I want to have cells K3:Z3 automatically populated using the same calculations as "Linear Trend" Auto Fill and K4:Z4 using "Growth Trend" Auto Fill without having to do it manually. The data in Row 2 will of course be continually updated with actual scores so the remaining empty month scores would need to be updated again. I am using this to come up with a more accurate prediction method than a simple trend line in Excel graphs. Thanks for any and all help.

******** ******************** ************************************************************************>Microsoft Excel - Auto Fill setup for questions.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutL14=
ABCDEFGHIJKLMNOPQRSTUVWXYZ1Months00.030.10.250.512345678910111215182124273033362Score10 1010 9887                3Linear Predicted10 1010 9887                4Growth Predicted10 1010 9887                5Auto filled Linear in Red10 1010 98877.066.666.275.885.485.094.74.33.913.513.122.732.331.941.551.156Auto filled Growth in blue10 1010 98877.146.826.526.235.955.695.435.194.964.744.534.334.143.953.783.617Actual scores10 1010 98877554456         Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

I am stuck and was wondering if someone has some input on how to make this happen.

CONCATENATE:
I have two list boxes in userform33 and you open this form when double clicking on a row that you want filled out. Listbox2 is the same as Listbox 3 ( for current status and new status ). So when someone chooses one option from each it will return (e.g. E / F) in column H on the row that was double clicked.

AUTO FILL
Same form (userform33) same method as filling out the row on double click. However. what I am trying to do is.

If Column D =A
then column I (in the row you doubled clicked) = "HALB"
then column J (in the row you doubled clicked) = "?"
then column K (in the row you doubled clicked) = "E"
then column M (in the row you doubled clicked) = "N"

If Sheets(1).Cells(7, 3).Value = "C80" And Sheets(2).Cells(22, 11).Value = "E" Then Sheet (2).Cells(22, 10).Value = "52"

If Column D =AD
then column I (in the row you doubled clicked) = "DOKU"
then column J (in the row you doubled clicked) = "-"
then column K (in the row you doubled clicked) = "-"
then column M (in the row you doubled clicked) = "N"

AND SO ON.......................

Here is my code for the sheet for the userform33 if it helps any

Dim UForm As UserForm33
Dim Sel_Row As Long
Dim Cont_Item As MSForms.Control
Dim Col_Name As String
Dim ListBox_Item As MSForms.ListBox
Dim Cnt1 As Integer
Dim Cnt2 As Integer
Dim Flag

Cancel = True

Set UForm = New UserForm33

Sel_Row = Target.Row

'POPULATE THE USERFORM FIELDS

'TEXT BOXES
UForm.TextBox4.Value = Me.Range("A" & Sel_Row).Value
UForm.TextBox2.Value = Me.Range("B" & Sel_Row).Value
UForm.TextBox5.Value = Me.Range("F" & Sel_Row).Value
UForm.TextBox6.Value = Me.Range("G" & Sel_Row).Value
UForm.TextBox7.Value = Me.Range("N" & Sel_Row).Value
UForm.TextBox3.Value = Me.Range("C" & Sel_Row).Value

' OPTION BUTTONS

For Each Cont_Item In UForm.Controls

If TypeName(Cont_Item) = "OptionButton" Then

Col_Name = ""

Select Case Cont_Item.Name

Case UForm.OptionButton16.Name
UForm.OptionButton17.Value = True
Col_Name = "L"

End Select

If Col_Name "" Then

If StrComp(Sheet4.Range(Col_Name & Sel_Row).Value, "Y", vbTextCompare) = 0 Then

Cont_Item.Value = True

End If

End If

End If

Next Cont_Item

'POPULATE LIST BOXES

UForm.ListBox1.List = Sheet4.Range("E2:E9").Value
UForm.ListBox4.List = Sheet4.Range("C2:C4").Value
UForm.ListBox2.List = Sheet4.Range("A2:A7").Value
UForm.ListBox3.List = Sheet4.Range("A2:A7").Value
UForm.ListBox5.List = Sheet4.Range("G2:G7").Value

'SELECT IN LISTS

For Each Cont_Item In UForm.Controls

If TypeName(Cont_Item) = "ListBox" Then

Set ListBox_Item = Cont_Item

Col_Name = ""

Select Case Cont_Item.Name

Case UForm.ListBox1.Name
Col_Name = "O"
Case UForm.ListBox4.Name
Col_Name = "E"
Case UForm.ListBox2.Name
Col_Name = "H"
Case UForm.ListBox3.Name
Col_Name = "H"
Case UForm.ListBox5.Name
Col_Name = "D"

End Select

End If

Next Cont_Item

Manual_Quit = False
UForm.Show

If Manual_Quit Then

Exit Sub

End If

'POPULATE FROM USERFORM

'TEXT BOXES
Me.Range("A" & Sel_Row).Value = UForm.TextBox4.Value
Me.Range("B" & Sel_Row).Value = UForm.TextBox2.Value
Me.Range("F" & Sel_Row).Value = UForm.TextBox5.Value
Me.Range("G" & Sel_Row).Value = UForm.TextBox6.Value
Me.Range("N" & Sel_Row).Value = UForm.TextBox7.Value
Me.Range("C" & Sel_Row).Value = UForm.TextBox3.Value

'OPTION BUTTONS
For Each Cont_Item In UForm.Controls

If TypeName(Cont_Item) = "OptionButton" Then

Col_Name = ""

Select Case Cont_Item.Name

Case UForm.OptionButton16.Name
Col_Name = "L"

End Select

If Col_Name "" Then

If Cont_Item.Value = True Then

Me.Range(Col_Name & Sel_Row).Value = "Y"

Else

Me.Range(Col_Name & Sel_Row).Value = "N"

End If

End If

End If

Next Cont_Item

'LIST BOXES

For Each Cont_Item In UForm.Controls

If TypeName(Cont_Item) = "ListBox" Then

Set ListBox_Item = Cont_Item

Col_Name = ""

Select Case Cont_Item.Name

Case UForm.ListBox1.Name
Col_Name = "O"
Case UForm.ListBox4.Name
Col_Name = "E"
Case UForm.ListBox2.Name
Col_Name = "H"
Case UForm.ListBox3.Name
Col_Name = "H"
Case UForm.ListBox5.Name
Col_Name = "D"

End Select

' Deleting the 9 rows below,,, will not give me an error... however, List boxes are not being entered into the cells

For Cnt2 = 0 To ListBox_Item.ListCount - 1

If ListBox_Item.Selected(Cnt2) = True Then

Me.Range(Col_Name & Sel_Row).Value = ListBox_Item.List(Cnt2)

Exit For

End If

Next Cnt2

Set ListBox_Item = Nothing

End If

Next Cont_Item

Set UForm = Nothing

Thanks for the help

Bill

I am a newbie at the whole VBA thing, and I am having some problems with my code. Whenever I run my code in excel with Calculations on Manual, the code runs fine, but when I turn calculation on automatic (which is what I need to make the rest of the workbook user friendly) my code will not even run. Can anyone please help me with this? I even tried using "Application.Calculation = xlManual" to turn off the auto calculation, but it did not help my code work. I attached the code for your convenience:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range
Dim lkup As Variant
Dim i As Integer

If Target.Address = "$C$14" Then

If Target.Value > 0 Then
Range("C40:D50").ClearContents
lkup = Application.VLookup(Range("c14"), ThisWorkbook.Worksheets("Parameters").Range("Adj_lookup"), 5, False)
Set R = ThisWorkbook.Worksheets("Parameters").Range(lkup)
For i = 1 To R.Rows.Count
Range("C40:C50").Cells(i, 1).Value = R.Cells(i, 1).Value
Range("C40:C50").Cells(i, 2).Value = R.Cells(i, 2).Value
Next i

End If
End If

End Sub

Hi,

hope you can help - just started using VBA recently - trying to build a userform that will send information to a spreadsheet that will record all entries made - all of the selections from the form will be present on individual rows in the spreadsheet

there are many fields on the userform (text entries, drop down boxes, radio buttons) and if one field has not been filled in by the user then a message box appears asking the user to fill in the relevant field

what im struggling with is how to stop the form sending any data to the spreadsheet when not all the fields have been completed.

currently:
- no data is sent to the spreadsheet if the first field on the userform (choice is 1 of 4 radio buttons) has not been completed (even when all of the other fields are complete)
- if the first field is complete it will always get sent to the spreadsheet...along with any other fields that have been completed
- all of the other fields on the userform are only ever sent when the first field is complete

my code is as follows: (included the first two fields from the form...field one is a choice of four options....field two is a drop down box selection....the other fields on the form are in the same sort of format eg ActiveCell.Offset(0, 2) = xxx)

Private Sub cmdOk_Click()

ActiveWorkbook.Sheets("Historical").Activate

Range("C8").Select

Do

If IsEmpty(ActiveCell) = False Then

ActiveCell.Offset(1, 0).Select

End If

Loop Until IsEmpty(ActiveCell) = True



If optPool = True Then

ActiveCell.Value = "Pool"

ElseIf optSnooker = True Then

ActiveCell.Value = "Snooker"

ElseIf optBilliards = True Then

ActiveCell.Value = "Billiards"

ElseIf optNineBall = True Then

ActiveCell.Value = "Nine Ball"

Else

MsgBox "Please complete the Option field.", vbExclamation, "Incomplete Data"

optPool.SetFocus

Exit Sub
End If


ActiveCell.Offset(0, 2) = cboLocation.Value

If cboLocation.Value = "" Then

MsgBox "Please complete the Location field.", vbExclamation, "Incomplete Data"

cboLocation.SetFocus

Exit Sub
End If

End Sub
Ive tried a few changes but am stuck - any help would be appreciated as well as any good VBA tutorial sites that anyone can recommend

Many thanks!

Hello excel experts,

I have problems with H2 from "Sell" sheet. I'm trying to get auto result in H2. IF the Coupon code in "Sell" sheet (C2:C32) have a match from any of (B2:B32) in "Buy" sheet, THEN it will calculate the value in "Sell" sheet G2, minus I2 in "Buy" sheet.
The problem is, I need to get the value based on the coupon code, and not depending from the same row on "Buy" sheet.

In "Cash balance" sheet, If there are any input in A2 from "Buy" sheet, it will input the same value (date) in A2. Also auto fill the C2 in "Cash balance"sheet with value from K2 in "Buy" sheet (the same row).
As for the B2 in "Cash balance" sheet, it will be looking up from I2 in "Sell" sheet, also fill out the A2 in "Cash balance" sheet from the same row in any of Value from A2 in the "Sell" sheet.
And the result will be in a consecutive row, based on the date.

I may be asking too much, but it's a great challenge if It was using only the excel formulas. I was googling for it and I know I may need to use the VBA or macros which is lacks of my ability.
So.. please help

Regards,
Peter

Hi All- I am new to VBA so I do apologize if this is an extremely easy fix. I have a command button on a Worksheet which opens a UserForm (DraftInputForm). The problem is that the Listbox which is part of the UserForm will not populate upon execution of the command button. It will only populate after the "Clear Form" button is pressed. I understand it is not that difficult to press the "clear form" button but as I am making this for others I would like it to be as easy as possible. See my code listed below. Any help would be greatly appreciated


	VB:
	
 DraftInputform_Initialize() 
     
     'empty PlayerNameTextbox
    PlayerNameTextbox.Value = "" 
     
     
     'empty TeamTextbox
    TeamTextbox.Clear 
     
     
     'fill TeamTextbox
    With TeamTextbox 
        .AddItem "Marathon Man" 
        .AddItem "Nomar Mr. Nice Guy" 
        .AddItem "Fighting Saints" 
        .AddItem "Aubrey Hasselhuff" 
        .AddItem "ConanO'Brien's" 
        .AddItem "LeeHarveyOswalts" 
    End With 
     
     
     'empty AmountTextbox
    AmountTextbox.Value = "" 
     
     
     'Set focus on PlayerNameTextbox
    PlayerNameTextbox.SetFocus 
     
     
End Sub 
Private Sub CancelButton_Click() 
     
     
    Unload Me 
     
     
End Sub 
 
 
Private Sub ClearButton_Click() 
     
     
    Call DraftInputform_Initialize 
     
     
End Sub 
 
 
Private Sub OKButton_Click() 
    Dim emptyRow As Long 
     
     
     'Make Sheet1 Active
    Sheets(1).Activate 
     
     
     'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1 
     
     
     'Export Data to worksheet
    Cells(emptyRow, 1).Value = PlayerNameTextbox.Value 
    Cells(emptyRow, 2).Value = AmountTextbox.Value 
    Cells(emptyRow, 3).Value = TeamTextbox.Value 
     
     
End Sub 
 
 
Private Sub DraftInputForm_Click() 
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi All,

I have recorded a macro to perform a vlookup then auto fill to the required amount of rows needed. I need to run this macro on other columns as well. My problem is when it goes to auto fill it uses the range from the column i recorded the macro on. I don't want to keep going in and changing the column letter each time i do it. What would i have to change the below to so it auto fills in the active column?


	VB:
	
Selection.AutoFill Destination:=Range("N2:N394"), Type:=xlFillDefault 
Range("N2:N394").Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I appreciate this is probably a very noob question but i've just started getting into Macros and VBA. I'm loving it either because of or despite of the fact its really frustrating me.

I have 3 workbooks:

Lead 1

This user enters defect data in cells A1 through A5This information then auto-fills cells A1 though A5 of the ENGís workbook Lead 2

This user enters defect data in rows A1 through A5This information then auto-fills cells A1 though A5 of the ENGís workbook ENG

This user will see the entries made by BOTH Lead 1 & 2.This user will then respond in cells A7 through A10; this will auto fill the cells A7 through A10 on both Lead 1 & 2 workbooks.
My first question is that I am using this formula to perform the auto fill. Is this the best method for achieving what I am looking for?
=IF(ISBLANK('[cell CPS.xlsx]Raw Materials'!B5),"",'[cell CPS.xlsx]Raw Materials'!B5)

My second question just dawned on me. How on earth do I perform the auto-fill function I am looking for, but have it where Leads 1 & 2 will jump to the next available blank row? Otherwise I am stuck creating multiple worksheets (which is not what I really want to do).

All comments and feedback is much appreciated. Thank you in advance.

Hello All ,
I am looking for assistance in figuring out an easy way of autofilling the days of the week in excel.
I am developing a monthly calendar to illustrate various resonsibilities of each worker
Row 3 is the days (Sat. through Sun)
Row 4 is the numbered days (1,2,3,4,5,6 etc..)

What I need is a way for excel to auto fill the days of the week as well as the numbered days. So I end up with something like
row3:SAT|SUN|MON|TUE|WED|THU|FRI|SAT|SUN|MON|TUE|WED|THU|FRI etc...
row4:1|2|3|4|5|6|7|8|9|10|11|12|13|14 etc...

Example:

	VB:
	
 CalBeta1() 
     ' Unprotect sheet if had previous calendar to prevent error.
    ActiveSheet.Protect DrawingObjects:=False, Contents:=False, _ 
    Scenarios:=False 
     ' Prevent screen flashing while drawing calendar.
    Application.ScreenUpdating = False 
     
     ' Set up error trapping.
    On Error Goto MyErrorTrap 
     
     ' Clear area a1:af35 including any previous calendar.
    Range("a1:af35").Clear 
     
     ' Use InputBox to get desired month and year and set variable
     ' MyInput.
    MyInput = InputBox("Type in Month and year for Calendar. [Format: January 2011] ") 
     
     ' Allow user to end macro with Cancel in InputBox.
    If MyInput = "" Then Exit Sub 
     
     ' Get the date value of the beginning of inputted month.
    StartDay = DateValue(MyInput) 
     
     ' Check if valid date but not the first of the month
     ' -- if so, reset StartDay to first day of month.
    If Day(StartDay)  1 Then 
        StartDay = DateValue(Month(StartDay) & "/1/" & _ 
        Year(StartDay)) 
    End If 
     
     ' BEGIN CALCULATION OF FIRST DAY
     ' Set variable and get which day of the week the month starts.
    DayofWeek = Weekday(StartDay) 
     ' Set variables to identify the year and month as separate
     ' variables.
    CurYear = Year(StartDay) 
    CurMonth = Month(StartDay) 
     
     ' Set variable and calculate the first day of the next month.
    FinalDay = DateSerial(CurYear, CurMonth + 1, 1) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Now, pinpointing the start is simple enough using CASE


	VB:
	
 DayofWeek 
Case 1 
    Range("b3").Value = "Sunday" 
    Range("b4").Value = 1 
Case 2 
    Range("b3").Value = "Monday" 
    Range("b4").Value = 1 
Case 3 
    Range("b3").Value = "Tuesday" 
    Range("b4").Value = 1 
Case 4 
    Range("b3").Value = "Wednesday" 
    Range("b4").Value = 1 
Case 5 
    Range("b3").Value = "Thursday" 
    Range("b4").Value = 1 
Case 6 
    Range("b3").Value = "Friday" 
    Range("b4").Value = 1 
Case 7 
    Range("b3").Value = "Saturday" 
    Range("b4").Value = 1 
End Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I figure there must be a nice clean way to autofill the days in both rows without having to make the whole macro look really ugly by manually coding in a calendar. Note I also need to populate all other months in the year
So Row 3 and Row 4 illustrate January. The following rows is each employee. After that, the next month starts and it repeats.

I hope I was clear enough ... sometimes the geek in me overpowers my writing ability and I spew out confusing junk.

Thanks to all that post!

Cheers

Hello everyone,

Long time reader, first time poster. This forum has helped me immensely Anyway now to my excel question.

I am working on a excel project where I have 2 worksheets (Excel 2007).

The 1st worksheet is a data dump of information. Fields are: Department Manager - Section Manager - Employee Name - Employee ID --etc. This has all the information I need for the 2nd sheet.

The 2nd worksheet has the following fields: Department Manager - Section Manager - Employee ID - Employee Name

Question:

What I basically want to do is depending on the Department Manger I select in Column A the rest will auto-populate with their respective Section Managers, Employees etc.

For example:

1. I select Manager John Smith..thus column A is auto-filled with John Smith in all column A (1 worksheet per Department Manager). This part is already done with a drop list in one cell and then it just is a simple = formula for all the cells.

2. The Section Managers are listed in Column B (each section manager is listed n number of times depending on how many employees fall under their section, also the Section managers name should be grouped together, so I can see that 1 sections managers staff together)

3. Next would be the Employee's ID and then the Employees name with them listed next to their respective Section Manager.

I know I could do a drove of IF functions and vlookups, but I was hoping to work smart and not harder when it came to this.

So basically the order would go Department Manager --> Section Manager --> Employee(s) ID --- Employee Name

Please see attached screenshot(s) of example files.

--------------------------------------------------------------------
Update:

I have decided to create a pivot table out of worksheet one (data dump). So now I have a list of all the employees broken apart by DM then SM then Expense code, Employee ID and Employee Name.

Now how would I use that to populate my worksheet 2? It only list the SM once and then places the employees in the tabular form. i want a way to auto fill from the pivot table source

I am having a bug this morning with Excel. I have a basic formula that I drag down and auto fill the spreadsheet. It looks like the formula is being copied but the result is not the formula result, it is just the top rows data being copied. When entering data one column at a time it works fine. What is wrong with my auto fill?

Hi,

As a minor problem - just wondering following:

In VBA Userform:

1. Indicating value A from sheet x in a Textbox1
2. Indicating value B from sheet x in a Textbox2

...and the problem:

3. Indicating value B/A [cell formula = %] from sheet x in a Textbox 3:

-> gives a problem: Textbox will handle the sheet value as a text - meaning that I can not get exact % value like 87 [%]...but something like 8734523525252354?

So - any ideas to deal with calculated % values in VBA userform appreciated,

Br, ToBe


No luck finding an answer? You could always try Google.