1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Code for a form

Discussion in 'Business Applications' started by JudyB, Jun 11, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. JudyB

    JudyB Thread Starter

    Joined:
    May 30, 2003
    Messages:
    98
    Good Morning :eek:
    I'm trying to build a nice data entry form in Excel. Has 5 required fields, the first 2 of which should default to name and todays date - which is at the top of the spreadsheet. I have 5 buttons: Next, Previous, Add, Delete, Close. I'm working on the code for those. But in the meantime, I'm hung up on the following problem:

    Some code that I have written keeps taking Name out of the spreadsheet cell A1. I can understand how that can happen when I run the form's code. But now when the form is closed, and I try to type the name back into A1, it will not stay. When I hit enter, it just disappears, not even in the formula bar. I've checked the formatting and nothing is odd there. If I insert a new row 1, the old row (now 2) works, and the new row 1 still has disappearing text. So something in my code is telling it to blank out cell A1. I don't understand how that can happen if I don't even run the code?

    The following is all the code behind the form. The only other code in the project is a button click on sheet one. The only code directly referring to Cell A1, is at the very bottom with the A1 reference bolded. Thanks for your help.
    -----------------------------------------------------------

    Dim CurrentRow As Long

    Private Sub cmdAdd_Click()
    ' Save form contents before changing rows:
    SaveRow
    ' If list is empty start in row 5:
    If Cells(5, 1).Value = "" Then
    CurrentRow = 1
    ' Set current row to first empty row:
    Else
    CurrentRow = ActiveSheet.UsedRange.Rows.Count + 1
    End If
    ' Clear the form for user to add new:
    ClearForm
    ' Set focus to Co #:
    txtCoNum.SetFocus
    End Sub

    Private Sub cmdClose_Click()
    ' Close the form w/o saving contents:
    Unload Me
    End Sub

    Private Sub cmdDelete_Click()

    ' If blank, give message:
    If txtTime.Text = "" Then
    MsgBox ("Nothing to Delete")
    ' Or Else Give option to Delete current row or cancel:
    ElseIf MsgBox("Are you sure you want to delete Co.# " + txtCoNum.Text + " Job# " + txtJobNum.Text + "?", vbQuestion + vbYesNo, "Confirm Delete") = vbYes Then
    Rows(CurrentRow).Delete
    ' Show contents of new current row in the form:
    LoadRow
    Else: CurrentRow = CurrentRow
    LoadRow
    End If
    End Sub

    Private Sub cmdNext_Click()
    ' Determine if there is new data to save -
    ' If blank, then keep current row as current row:
    If txtYourName.Text = "" Or txtDateWork.Text = "" Or txtCoNum.Text = "" Or txtJobNum.Text = "" Or txtTime.Text = "" Then
    Msg = "You must complete all fields except optional Comments"
    CurrentRow = CurrentRow
    LoadRow
    txtCoNum.SetFocus
    ' Otherwise save form contents, go to next row, and load data:
    Else
    SaveRow
    CurrentRow = CurrentRow + 1
    LoadRow
    ' Set focus to Co # textbox:
    txtCoNum.SetFocus
    End If
    End Sub

    Private Sub cmdPrev_Click()
    ' Show previous only if not already in first row:
    If CurrentRow > 5 Then
    ' Save form contents before changing rows:
    SaveRow
    ' Move back one row number:
    CurrentRow = CurrentRow - 1
    ' Show contents of new row in the form:
    LoadRow
    End If
    End Sub

    Private Sub UserForm_Activate()
    ' If list is empty start in row 5:
    If Cells(5, 1).Value = "" Then
    CurrentRow = 1
    ' Set current row to first empty row:
    Else
    CurrentRow = ActiveSheet.UsedRange.Rows.Count + 1
    End If
    ' Clear the form for user to add new:
    ClearForm
    ' Set focus to Co #:
    txtCoNum.SetFocus
    End Sub

    Private Sub LoadRow()
    'Subroutine to load all row contents into form:
    txtYourName.Text = Cells(CurrentRow, 2).Value
    txtDateWork.Text = Cells(CurrentRow, 3).Value
    txtCoNum.Text = Cells(CurrentRow, 1).Value
    txtJobNum.Text = Cells(CurrentRow, 4).Value
    txtTime.Text = Cells(CurrentRow, 5).Value
    txtComment.Text = Cells(CurrentRow, 6).Value
    End Sub

    Private Sub SaveRow()
    'Subroutine to save form contents into a row of spreadsheet:
    Cells(CurrentRow, 1).Value = txtCoNum.Text
    Cells(CurrentRow, 2).Value = txtYourName.Text
    Cells(CurrentRow, 3).Value = txtDateWork.Text
    Cells(CurrentRow, 4).Value = txtJobNum.Text
    Cells(CurrentRow, 5).Value = txtTime.Text
    Cells(CurrentRow, 6).Value = txtComment.Text
    End Sub

    Private Sub ClearForm()
    'Subroutine to clear form:
    txtCoNum.Text = ""
    txtJobNum.Text = ""
    txtTime.Text = ""
    txtComment.Text = ""
    txtDateWork.Text = Cells(1, 4).Value
    txtYourName.Text = Cells(1, 1).Value
    End Sub
     
  2. xXLdev

    xXLdev

    Joined:
    May 17, 2004
    Messages:
    34
    I have tried recreating the problem that you describe but with no success.
    What version of Excel are you using?
    Can you post the file? It would save others having to recreate the form.

    I think I found a bug in your code.

    In cmdAdd_Click and UserForm_Activate I think that the following line:
    CurrentRow = 1
    needs to be changed to
    CurrentRow = 5
    given your comments in the code.

    Is there any other information that you can give?
     
  3. JudyB

    JudyB Thread Starter

    Joined:
    May 30, 2003
    Messages:
    98
    Thanks. I did find that discrepancy (Row 1, should be Row 5). I had rearranged the layout of the spreadsheet and hadn't fixed all the code yet.

    I'm using Excel 2002. I don't think I can attach an .xls file. But I'll try to zip the file and attach that. The Cell A1 (yellow background), is where the text disappears when you type it in. I'm pretty sure it's related to the code.

    Thanks for your help.
     

    Attached Files:

  4. xXLdev

    xXLdev

    Joined:
    May 17, 2004
    Messages:
    34
    OK, that was painful.

    The problem is on your TimeEntryForm on the ListBox1 control. You have set ControlSource to a1. So that is where the value selected in the listbox is stored. The problem is since nothing is selected in the listbox, because it is empty, then a1 is set to nothing. So either clear the value in ControlSource or set it somewhere else that you are not trying to also enter data.

    Hope this help.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/237965

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice