PDA

View Full Version : help to eliminate repeated inputs



av8tordude
12-11-2008, 12:14 PM
I have a userform with 1 textbox, 12 checkboxes, and 1 enter button. The columns start at AD9 and end at AT9. When I double-click on an empty cell in column AD to call the userform, I enter the aircraft's name into the text box and define the preferences I want to use for that aircraft by clicking on any of the 12 checkboxes. When I click the enter button, the aircraft name should be entered in the next available space starting at AD9 and whatever checkbox(s) are checked, it should place an "x" into the corresponding column next to he aircrafts name

If I want to edit an existing aircraft preferences,I double-click on the existing aircraft to call the userform. When the userform appears, the aircraft name should appear in the text box and the checked boxes should already be filled in according to the "x" in the respective column into the userform as defined by my preferences so it can reviewed and/or change.

Can someone help me with a macro to accomplish this?

nst1107
12-11-2008, 01:08 PM
On UserForm_Initialize(), set TextBox1 = the active cell, then go in order and set each checkbox to True or False according to whether its respective cell offset the active cell = X or no X.

On CommandButton1_Click(), before the line to unload the form, set the active cell = TextBox1, then go in order and give each cell an X or vbNullString acording to its corresponding checkbox's value.

If your checkboxes aren't indexed in the same order as they appear accross the columns in your worksheet, you'll have to write a line for each one of the checkboxes, otherwise you can use a loop and go by index number.

An example:

CheckBox1 = IIf(ActiveCell.Offset(, 1).Value = "X", True, False)

av8tordude
12-11-2008, 01:22 PM
Thank you, nate, for posting.

I'm not clear on what you are suggesting.

Bob Phillips
12-11-2008, 01:41 PM
Try this

Philcjr
12-11-2008, 01:54 PM
This should get you started.... have fun :hi:

av8tordude
12-11-2008, 02:22 PM
This should get you started.... have fun :hi:

Thank you for your help XLD & Phil. Phil I tried your worksheet and when I clicked on an empty cell to enter a new aircraft, it gives me an error. the error states "Object variable or With block variable not set" and it highlights

Let Row = Worksheets("Sheet1").Range("AD9:AD65526").Find(txAircraft.Value, , , xlWhole).Row

mdmackillop
12-11-2008, 03:37 PM
Private Sub bnEnter_Click()
Dim c As Range
Dim Row As Long

Set c = Worksheets("Sheet1").Range("AD1:AD65526").Find(txAircraft.Value, , , xlWhole)
If c Is Nothing Then
Row = Cells(Rows.Count, "AD").End(xlUp).Row + 1
Else
Row = c.Row
End If

Range("AD" & Row).Value = txAircraft.Value
If ckSELand.Value = True Then 'etc.

av8tordude
12-11-2008, 03:48 PM
Private Sub bnEnter_Click()
Dim c As Range
Dim Row As Long

Set c = Worksheets("Sheet1").Range("AD1:AD65526").Find(txAircraft.Value, , , xlWhole)
If c Is Nothing Then
Row = Cells(Rows.Count, "AD").End(xlUp).Row + 1
Else
Row = c.Row
End If

Range("AD" & Row).Value = txAircraft.Value
If ckSELand.Value = True Then 'etc.


Thank you for you help. Its working. One last request. I've been trying to figure this one out on my own, but unfortunate could not. When I click on a cell in column AD, the userform appears every time. The userform should not appear unless I double click the cell in column AD. Any suggestions. I've tried changing the userform_initialze to Dbl clk, but it didn't work.

mdmackillop
12-11-2008, 03:57 PM
Change the first line of the code in the Sheet1 module to
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

av8tordude
12-11-2008, 04:05 PM
Thank you guys. :friends: It working.

av8tordude
12-12-2008, 01:56 PM
When I double-click on an empty cell in column A in the spreadsheet, the userform appears When I enter one of the aircraft listed in column AD (ex. C172) into the ?aircraft type ?txt box in the userform and I enter (ex. 2.5) into the total time box in the userform, I would like the other boxes to be auto-fill by the same number as the total time box base upon whatever ?x? is mark in columns AE:AT.

Example: C172 preferences are SEL, PIC, X-CTRY. 2.5 should automatically fill the boxes in the userform as define by the aircraft preferences.

C172
Total Time: 2.5
SEL: 2.5
PIC: 2.5
X-CTRY: 2.5

I also would like the ability to change what is auto-filled in the userform. Ex. If X-CTRY is auto-filled with 2.5 and I want to either delete or change the number to 1.0, it should not affected the preferences


Can someone write a code that will accomplish this?

av8tordude
12-13-2008, 08:43 AM
I've added a combo box instead of a textbox to my userform so I can select the aircraft from the choices in column AD. How can I change this code so that when I select the aircraft from the drop down combo box, it autofills the checkboxes. What I intend to do is but a box on the spreadsheet to call the userform instead of double clicking on column AD.

Private Sub UserForm_Initialize()
Dim Row As Long
ACtxt.Value = ActiveCell.Value
Let Row = Worksheets("Logbook").Range("AD9:AD100").Find(ACtxt.Value, , , xlWhole).Row
If Row = 0 Then Exit Sub
If Range("AE" & Row).Value = "X" Then SELck.Value = True
If Range("AF" & Row).Value = "X" Then SESck.Value = True
If Range("AG" & Row).Value = "X" Then MELck.Value = True
If Range("AH" & Row).Value = "X" Then OPT1ck.Value = True
If Range("AI" & Row).Value = "X" Then OPT2ck.Value = True
If Range("AJ" & Row).Value = "X" Then OPT3ck.Value = True
If Range("AK" & Row).Value = "X" Then OPT4ck.Value = True
If Range("AL" & Row).Value = "X" Then OPT5ck.Value = True
If Range("AM" & Row).Value = "X" Then NIGHTck.Value = True
If Range("AN" & Row).Value = "X" Then FLTSIMck.Value = True
If Range("AO" & Row).Value = "X" Then XCTRYck.Value = True
If Range("AP" & Row).Value = "X" Then SOLOck.Value = True
If Range("AQ" & Row).Value = "X" Then PICck.Value = True
If Range("AR" & Row).Value = "X" Then SICck.Value = True
If Range("AS" & Row).Value = "X" Then DUALck.Value = True
If Range("AT" & Row).Value = "X" Then CFIck.Value = True
ENTERcmd.SetFocus
End Sub
Private Sub ENTERcmd_Click()
Dim c As Range
Dim Row As Long

Set c = Worksheets("Logbook").Range("AD9:AD100").Find(ACtxt.Value, , , xlWhole)
If c Is Nothing Then
Row = Cells(Rows.Count, "AD").End(xlUp).Row + 1
Else
Row = c.Row
End If

Range("AD" & Row).Value = ACtxt.Value
If SELck.Value = True Then Range("AE" & Row).Value = "X" Else Range("AE" & Row).Value = ""
If SESck.Value = True Then Range("AF" & Row).Value = "X" Else Range("AF" & Row).Value = ""
If MELck.Value = True Then Range("AG" & Row).Value = "X" Else Range("AG" & Row).Value = ""
If OPT1ck.Value = True Then Range("AH" & Row).Value = "X" Else Range("AH" & Row).Value = ""
If OPT2ck.Value = True Then Range("AI" & Row).Value = "X" Else Range("AI" & Row).Value = ""
If OPT3ck.Value = True Then Range("AJ" & Row).Value = "X" Else Range("AJ" & Row).Value = ""
If OPT4ck.Value = True Then Range("AK" & Row).Value = "X" Else Range("AK" & Row).Value = ""
If OPT5ck.Value = True Then Range("AL" & Row).Value = "X" Else Range("AL" & Row).Value = ""
If NIGHTck.Value = True Then Range("AM" & Row).Value = "X" Else Range("AM" & Row).Value = ""
If FLTSIMck.Value = True Then Range("AN" & Row).Value = "X" Else Range("AN" & Row).Value = ""
If XCTRYck.Value = True Then Range("AO" & Row).Value = "X" Else Range("AO" & Row).Value = ""
If SOLOck.Value = True Then Range("AP" & Row).Value = "X" Else Range("AP" & Row).Value = ""
If PICck.Value = True Then Range("AQ" & Row).Value = "X" Else Range("AQ" & Row).Value = ""
If SICck.Value = True Then Range("AR" & Row).Value = "X" Else Range("AR" & Row).Value = ""
If DUALck.Value = True Then Range("AS" & Row).Value = "X" Else Range("AS" & Row).Value = ""
If CFIck.Value = True Then Range("AT" & Row).Value = "X" Else Range("AT" & Row).Value = ""

Unload Me
End Sub

mdmackillop
12-13-2008, 09:34 AM
Can you repost your workbook with the new userform?

Demosthine
12-13-2008, 07:02 PM
Good Evening.

Here is the basis for what you are wanting to do. The code is well commented, so see below:


' I recommend always using this Option. It requires that all Variables
' be declared prior to being used. This prevents typographical errors
' and defining the same Variable more than one time.
Option Explicit

' Instead of having the User type the Aircraft type into the
' ComboBox, we'll populate it based on the Worksheet.
Private Sub UserForm_Initialize()
' Declare a Variable to access the Range that will have a list of the
' possible Aircraft.
Dim rngAircraft As Range
' Set the Range equal to the location of the first Aircraft (AD3) all
' the way to the end of the region. Use the End Method with the
' Argument xlDown to move to the buttom of the Range.
Set rngAircraft = Range("AD3", _
Worksheets("Preferences").Range("AD3").End(xlDown))
' Set the ComboBox's RowSource Property to the Range we just defined.
' This prevents us from having to explicitly define each entry.
ACLIST.RowSource = rngAircraft.Address
End Sub

' When the User exits the TextBox, we will determine if the User has
' specified an Aircraft yet. Then it will evaluate the Worksheet
' and insert the current text into the appropriate TextBoxes.
Private Sub txtTOTAL_Exit(ByVal Cancel As MSForms.ReturnBoolean)
' Define a Variable to store the Aircraft's Index as it correlates to
' the ComboBox. This is the equivalent of the Row Offset that we will
' need to process the remaining data.
Dim intAircraft As Integer
' Assign the ComboBox's current ListIndex to the Variable.
intAircraft = ACLIST.ListIndex + 1
' If the ListIndex returned a -1 Value, there is no item selected yet and
' if the TextBox does not contain any data, we will stop processing at that point.
If intAircraft = 0 Or txtTOTAL.Value = "" Then
' Do not process anything further.
Exit Sub
' An Aircraft was selected. Because of the method we used to add the
' Aircraft to the ComboBox, this is also the number of Rows below our
' Reference Cell of AD2 that the Aircraft's data is on.
Else
' Define a Variable that will store the Total Time that we want to insert
' into the other TextBoxes.
Dim dblTotalTime As Double
dblTotalTime = txtTOTAL.Text
' We will cycle through a bunch of If Statements to determine whether
' we will assign the
With Worksheets("Preferences")
' Single-Engine Land
If UCase(.Cells(2 + intAircraft, 31).Value) = "X" Then
' The Preferences are set to duplicate the Total Time.
txtSEL.Text = dblTotalTime
Else
' The Preferences are not set to duplicate the Total Time.
txtSEL.Text = ""
End If
' Single-Engine Sea
If UCase(.Cells(2 + intAircraft, 32).Value) = "X" Then
' The Preferences are set to duplicate the Total Time.
txtSES.Text = dblTotalTime
Else
' The Preferences are not set to duplicate the Total Time.
txtSES.Text = ""
End If
' Multi-Engine Land
If UCase(.Cells(2 + intAircraft, 33).Value) = "X" Then
' The Preferences are set to duplicate the Total Time.
txtMEL.Text = dblTotalTime
Else
' The Preferences are not set to duplicate the Total Time.
txtMEL.Text = ""
End If

' OPT1

' ...

' Solo
If UCase(.Cells(2 + intAircraft, 42).Value) = "X" Then
' The Preferences are set to duplicate the Total Time.
txtSOLO.Text = dblTotalTime
Else
' The Preferences are not set to duplicate the Total Time.
txtSOLO.Text = ""
End If
End With
End If
End Sub


Scott

av8tordude
12-19-2008, 09:09 AM
I'm having a small problem. When I delete all the aircraft listed in column AD to begin a new spreadsheet and I begin the process of adding a new aircraft in the frmPref form, it gives an error of...Run-Time error '13':

When I debug, it highlights...

If UBound(acLists, 1) = 1 Then

Can you offer a solution? Thank you for your help.

Bob Phillips
12-19-2008, 10:04 AM
Not with that little information.

av8tordude
12-19-2008, 10:26 AM
Background...

Starting at Column AD8, column AD houses a list Aircrafts. If AD8 & AD9 already have an aircraft listed, I don't get an error, but if I want to start new with AD8 & AD9 empty, thats when I get an error. this is the complete code to the "add button".


Private Sub Addcmd_Click()
Dim c As Range, lRow As Long, i As Long

On Error Resume Next
Set c = Range("AD8", Range("ad" & Rows.Count).End(xlUp)).Find(ACLIST.Value, , , xlWhole)
On Error GoTo 0
If c Is Nothing Then
lRow = Cells(Rows.Count, "AD").End(xlUp).Row + 1
Else
lRow = c.Row
End If

Range("AD" & lRow).Value = UCase(ACLIST.Value)
Range("ae" & lRow).Resize(UBound(Ctrls) + 1).ClearContents
For i = 0 To UBound(Ctrls)
If Ctrls(i) Then Cells(lRow, i + 31) = "X"
Next
Unload Me
UpdateAcList
If UBound(acLists, 1) = 1 Then
frmLogbook.ACLIST.Clear
frmLogbook.ACLIST.AddItem acLists
Else
frmLogbook.ACLIST.Clear
frmLogbook.ACLIST.List = acLists
End If
frmLogbook.Show
End Sub

av8tordude
12-19-2008, 09:07 PM
I've searched the forum for a possible solution, used the Help feature in XL. Unfortunately, I at an impasse. Can someone explain to me why I keep getting this error and respectfully offer a solution?

GTO
12-20-2008, 08:59 AM
Could you repost the wb?

av8tordude
12-20-2008, 10:07 AM
Could you repost the wb?

Workbook attached

GTO
12-20-2008, 11:09 AM
Greetings av8tor,
Reference:
...
UpdateAcList
If UBound(acLists, 1) = 1 Then
frmLogbook.ACLIST.Clear
...
When you add your first plane, 'acLists' only contains cell AD8. Thus, there is no array for UBound to look at.
Hope this helps,
Mark

lucas
12-20-2008, 11:19 AM
You are making it hard on yourself and everyone trying to help you. This question relates directly to all of these threads you have created. Why add confusion to a complex problem? Keep all of your questions regarding one file to one thread.

We have come to expect a little project creep but when you scatter out your questions like this it is very discouraging to anyone who might be interested in helping you.......



http://vbaexpress.com/forum/showthread.php?t=24191

http://vbaexpress.com/forum/showthread.php?t=24174

http://vbaexpress.com/forum/showthread.php?t=24260

av8tordude
12-20-2008, 11:27 AM
Greetings av8tor,
Reference:
...
UpdateAcList
If UBound(acLists, 1) = 1 Then
frmLogbook.ACLIST.Clear
...
When you add your first plane, 'acLists' only contains cell AD8. Thus, there is no array for UBound to look at.
Hope this helps,
Mark

Thank you for your response...

I kinda figure that was happening, but when I insert 2 aircraft in column AD manually, the array works fine. If I enter 1 aircraft manually in column AD, than when I click on a cell in column A8+, I get the same error. how do i get the code to recognise that it starting a new form?