Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet problem???
Why does only one of my sheets show rows 1,2,960? Every cell has a dropdown
box showing that I did not create, at least that I know of, maybe within the code I am using does. Here is the codes that reference the sheet. Private Sub BtnAdd_Click() Dim iRow As Long Dim WS As Worksheet Dim intMtoprow As Integer Dim dept As String Dim x As Integer Dim R As Integer Dim strCell As Variant Dim y As Integer Application.EnableEvents = False Set WS = Worksheets("ProCode") 'find first empty row in database iRow = WS.Cells(Rows.Count, 1) _ .End(xlUp).Offset(1, 0).Row 'check for the product name If Trim(Me.CbxProd.Value) = "" Then Me.CbxProd.SetFocus MsgBox "Please enter the product name" Exit Sub End If 'creates the MSDS# dept = Me.CboDept.Text y = 0 intMtoprow = WS.Range("M1000").End(xlUp).Row For R = 2 To intMtoprow strCell = WS.Cells(R, 13).Value If InStr(strCell, dept) = 1 And _ IsNumeric(Mid(strCell, Len(dept) + 1)) Then x = CInt(Mid(strCell, Len(dept) + 1)) If x y Then y = x End If End If Next R 'copy the data to the database WS.Cells(iRow, 2).Value = Me.CbxProd.Value WS.Cells(iRow, 3).Value = IIf(Me.CkBox1.Value, "Yes", "No") WS.Cells(iRow, 4).Value = IIf(Me.CkBox2.Value, "Yes", "No") WS.Cells(iRow, 5).Value = IIf(Me.CkBox3.Value, "Yes", "No") WS.Cells(iRow, 6).Value = Me.CboFire.Value WS.Cells(iRow, 7).Value = Me.CboHealth.Value WS.Cells(iRow, 8).Value = Me.CboReact.Value WS.Cells(iRow, 9).Value = Me.CboSpec.Value WS.Cells(iRow, 10).Value = Me.CboDisp.Value WS.Cells(iRow, 11).Value = Me.TxtQuan.Value WS.Cells(iRow, 12).Value = Me.TxtDate.Value WS.Cells(iRow, 13).Value = dept & Format(y + 1, "00#") Application.EnableEvents = True 'the sort will fire with this line. WS.Cells(iRow, 1).Value = Me.CbxMfg.Value 'clears all boxes Me.CbxMfg.Value = "" Me.CbxProd.Value = "" Me.CkBox1.Value = False Me.CkBox2.Value = False Me.CkBox3.Value = False Me.CboFire.Value = "" Me.CboHealth.Value = "" Me.CboReact.Value = "" Me.CboSpec.Value = "" Me.CboDisp.Value = "" Me.TxtQuan.Value = "" Me.TxtDate.Value = "" End Sub Private Sub BtnClose_Click() FrmProduct.Hide StrtUpFrm.Show End Sub Private Sub BtnDelete_Click() Dim fRow As Long On Error GoTo ender 'finds product name in column 'B' _ then deletes the entire row Sheets("ProCode").Columns(2).Find(What:=CbxProd.Va lue, _ After:=Cells(5000, 2), LookIn:=xlFormulas, _ LookAT:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False).EntireRow.Delete Exit Sub 'clears all boxes Me.CbxMfg.Value = "" Me.CbxProd.Value = "" Me.CkBox1.Value = False Me.CkBox2.Value = False Me.CkBox3.Value = False Me.CboFire.Value = "" Me.CboHealth.Value = "" Me.CboReact.Value = "" Me.CboSpec.Value = "" Me.CboDisp.Value = "" Me.TxtQuan.Value = "" Me.TxtDate.Value = "" ender: MsgBox "Value not found" End Sub Private Sub CbxMfg_Change() Dim S As String Dim V As Variant Dim J As Range 'captures CbxMan text and stores as a string S = Me.CbxMfg.Text 'compares S with whats already in the database(MANCODE) V = Application.Match(S, Worksheets("MANCODE").Range("A2:A1000"), 0) 'If S is not in the database then it opens FrmManu If IsError(V) = True Then 'FrmProduct.Hide 'FrmManu.Show End If 'If S is in the database then it finds each instance of S _ in database(ProCode) and for each S it populates CbxProd with _ that product name and sets the focus to CbxProd for the user _ to select If IsError(V) = False Then With Me.CbxProd .Clear For Each J In Worksheets("ProCode").Range("A2:A1000") If J.Text = S Then ..AddItem J(1, 2) End If Next J ..SetFocus If .ListCount 0 Then ..ListIndex = 0 End If End With End If End Sub Private Sub TxtDate_DblClick(ByVal Cancel As MSForms.ReturnBoolean) FrmCalendar.Show End Sub Private Sub UserForm_Initialize() 'populates each combobox list CbxMfg.RowSource = Worksheets("MANCODE").Range("A2:A1000").Address(ex ternal:=True) CboFire.RowSource = Worksheets("Lists").Range("D2:D5").Address(externa l:=True) CboHealth.RowSource = Worksheets("Lists").Range("D2:D5").Address(externa l:=True) CboReact.RowSource = Worksheets("Lists").Range("D2:D5").Address(externa l:=True) CboDisp.RowSource = Worksheets("Lists").Range("E2:E4").Address(externa l:=True) CboDept.RowSource = Worksheets("Lists").Range("C2:C10").Address(extern al:=True) CboSpec.RowSource = Worksheets("lists").Range("F2:F4").Address(externa l:=True) 'clears all boxes Me.CbxMfg.Value = "" Me.CbxProd.Value = "" Me.CkBox1.Value = False Me.CkBox2.Value = False Me.CkBox3.Value = False Me.CboFire.Value = "" Me.CboHealth.Value = "" Me.CboReact.Value = "" Me.CboSpec.Value = "" Me.CboDisp.Value = "" Me.TxtQuan.Value = "" Me.TxtDate.Value = "" End Sub Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then 'Cancel = False FrmProduct.Hide StrtUpFrm.Show End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ScrollBar Sheet problem | Excel Programming | |||
Problem Sheet on its way | Excel Discussion (Misc queries) | |||
Sheet name problem | Excel Programming | |||
Problem activating a sheet | Excel Programming | |||
Problem pasting a row from a hidden sheet to the first free row on another visible sheet | Excel Programming |