none
Case Question RRS feed

  • Question

  • I am trying to use then same form multiple times based on the Case picked. I can’t get it to work. Here is what I have for Case = 1:

    Private Sub CmdRiskPL_Change()

    Dim meTag As Variable If CmdRiskPL = "MEDIUM" And pRev = "0" Then

        With ActiveDocument

            .Variables("meTag").Value = 1

        End With

        TextBox7.Locked = False

        TextBox7 = ""

        lstTextBox3.Clear

        UserForm1.Show

        TextBox7.Locked = True

        TextBox5 = CmdRiskPL

    End If

    .

    .

    .

    Private Sub UserForm_Initialize()

    Application.ScreenUpdating = False

    Dim sourcedoc As Document, h As Integer, j As Integer, myitem As Range, m As Long, n As Long

    Select Case meTag

    Case Is = 1

    If frmplan("CmdRiskPL") = "MEDIUM" Then

        Set sourcedoc = Documents.Open(FileName:="S:\Maintenance\Medium Worksheet.doc")

        Caption = "Work Control Medium Risk Assessment Worksheet"

    ElseIf frmplan("CmdRiskPL") = "HIGH" Then

        Set sourcedoc = Documents.Open(FileName:="S:\Maintenance\Risk Worksheet.doc")

        Caption = "Work Control High Risk Mitigating Worksheet"

    End If

    h = sourcedoc.Tables(1).Rows.Count - 1

    j = sourcedoc.Tables(1).Columns.Count

    ListBox1.ColumnCount = j

    ListBox1.ColumnWidths = "90;0"

    Dim myArray1() As Variant

    ReDim myArray1(h, j)

    For n = 0 To j - 1

        For m = 0 To h - 1

            Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range

            myitem.End = myitem.End - 1

            myArray1(m, n) = myitem.Text

        Next m

    Next n

    ListBox1.List() = myArray1

    sourcedoc.Close SaveChanges:=wdDoNotSaveChanges

    Application.ScreenUpdating = True

    For i = 0 To frmplan.lstTextBox3.ListCount - 1

        j = frmplan.lstTextBox3.List(i, 0)

        ListBox1.Selected(j) = True

    Next i

    End Select

    End Sub

    .

    .

    .

    Private Sub CommandButton1_Click()

    Dim lngCount As Long

    Dim strPicks As String

    lngCount = 0

    Select Case meTag

    Case Is = 1

    frmplan.lstTextBox3.Clear

    For i = 0 To ListBox1.ListCount - 1

    If ListBox1.Selected(i) Then

        lngCount = lngCount + 1

        If lngCount = 1 Then

            strPicks = ListBox1.List(i)

        Else

            strPicks = strPicks & vbCr & ListBox1.List(i)

        End If

        frmplan.lstTextBox3.AddItem i

    End If

    Next i

    frmplan.TextBox7 = strPicks

    Unload Me

    End Select

    End Sub

    Sunday, September 14, 2014 5:15 PM

Answers

  • I figured it out. It should go as follows:

    Private Sub CmdRiskPl1_Change()
    Me.Tag = 2

    If CmdRiskPL1 = "MEDIUM" And pRev = "0" Then
        TextBox8.Locked = False
        TextBox8 = ""
        lstTextBox4.Clear
        UserForm1.Show
        TextBox8.Locked = True
        TextBox6 = CmdRiskPL1

    End If

    Then in my UserForm it should read:

    Private Sub UserForm_Initialize()
    Application.ScreenUpdating = False
    Dim sourcedoc As Document, h As Integer, j As Integer, myitem As Range, m As Long, n As Long


    Dim myForm As frmplan
    Set myForm = New frmplan
    Select Case myForm.Tag
    Case Is = 1
    If frmplan("CmdRiskPL") = "MEDIUM" Then
        Set sourcedoc = Documents.Open(FileName:="S:\Maintenance\Maint_Programs\Planning\Impact_Template\Planner Industrial Medium Risk Bases Worksheet.doc")
        Caption = "Work Control Medium Risk Assessment Worksheet"
    ElseIf frmplan("CmdRiskPL") = "HIGH" Then
        Set sourcedoc = Documents.Open(FileName:="S:\Maintenance\Maint_Programs\Planning\Impact_Template\Planner Industrial High Risk Bases Worksheet.doc")
        Caption = "Work Control High Risk Mitigating Worksheet"
    End If
    h = sourcedoc.Tables(1).Rows.Count - 1
    j = sourcedoc.Tables(1).Columns.Count
    ListBox1.ColumnCount = j
    ListBox1.ColumnWidths = "90;0"
    Dim myArray1() As Variant
    ReDim myArray1(h, j)
    For n = 0 To j - 1
        For m = 0 To h - 1
            Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
            myitem.End = myitem.End - 1
            myArray1(m, n) = myitem.Text
        Next m
    Next n
    ListBox1.List() = myArray1
    sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
    Application.ScreenUpdating = True

    For i = 0 To frmplan.lstTextBox3.ListCount - 1
        j = frmplan.lstTextBox3.List(i, 0)
        ListBox1.Selected(j) = True
    Next i
    End Select

    • Marked as answer by fuzzhead58 Sunday, September 14, 2014 7:30 PM
    Sunday, September 14, 2014 7:30 PM

All replies

  • I figured it out. It should go as follows:

    Private Sub CmdRiskPl1_Change()
    Me.Tag = 2

    If CmdRiskPL1 = "MEDIUM" And pRev = "0" Then
        TextBox8.Locked = False
        TextBox8 = ""
        lstTextBox4.Clear
        UserForm1.Show
        TextBox8.Locked = True
        TextBox6 = CmdRiskPL1

    End If

    Then in my UserForm it should read:

    Private Sub UserForm_Initialize()
    Application.ScreenUpdating = False
    Dim sourcedoc As Document, h As Integer, j As Integer, myitem As Range, m As Long, n As Long


    Dim myForm As frmplan
    Set myForm = New frmplan
    Select Case myForm.Tag
    Case Is = 1
    If frmplan("CmdRiskPL") = "MEDIUM" Then
        Set sourcedoc = Documents.Open(FileName:="S:\Maintenance\Maint_Programs\Planning\Impact_Template\Planner Industrial Medium Risk Bases Worksheet.doc")
        Caption = "Work Control Medium Risk Assessment Worksheet"
    ElseIf frmplan("CmdRiskPL") = "HIGH" Then
        Set sourcedoc = Documents.Open(FileName:="S:\Maintenance\Maint_Programs\Planning\Impact_Template\Planner Industrial High Risk Bases Worksheet.doc")
        Caption = "Work Control High Risk Mitigating Worksheet"
    End If
    h = sourcedoc.Tables(1).Rows.Count - 1
    j = sourcedoc.Tables(1).Columns.Count
    ListBox1.ColumnCount = j
    ListBox1.ColumnWidths = "90;0"
    Dim myArray1() As Variant
    ReDim myArray1(h, j)
    For n = 0 To j - 1
        For m = 0 To h - 1
            Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
            myitem.End = myitem.End - 1
            myArray1(m, n) = myitem.Text
        Next m
    Next n
    ListBox1.List() = myArray1
    sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
    Application.ScreenUpdating = True

    For i = 0 To frmplan.lstTextBox3.ListCount - 1
        j = frmplan.lstTextBox3.List(i, 0)
        ListBox1.Selected(j) = True
    Next i
    End Select

    • Marked as answer by fuzzhead58 Sunday, September 14, 2014 7:30 PM
    Sunday, September 14, 2014 7:30 PM
  • I figured it out. It should go as follows:

    Private Sub CmdRiskPl1_Change()
    Me.Tag = 2

    If CmdRiskPL1 = "MEDIUM" And pRev = "0" Then
        TextBox8.Locked = False
        TextBox8 = ""
        lstTextBox4.Clear
        UserForm1.Show
        TextBox8.Locked = True
        TextBox6 = CmdRiskPL1

    End If

    Then in my UserForm it should read:

    Private Sub UserForm_Initialize()
    Application.ScreenUpdating = False
    Dim sourcedoc As Document, h As Integer, j As Integer, myitem As Range, m As Long, n As Long


    Dim myForm As frmplan
    Set myForm = New frmplan
    Select Case myForm.Tag
    Case Is = 1
    If frmplan("CmdRiskPL") = "MEDIUM" Then
        Set sourcedoc = Documents.Open(FileName:="S:\Maintenance\Maint_Programs\Planning\Impact_Template\Planner Industrial Medium Risk Bases Worksheet.doc")
        Caption = "Work Control Medium Risk Assessment Worksheet"
    ElseIf frmplan("CmdRiskPL") = "HIGH" Then
        Set sourcedoc = Documents.Open(FileName:="S:\Maintenance\Maint_Programs\Planning\Impact_Template\Planner Industrial High Risk Bases Worksheet.doc")
        Caption = "Work Control High Risk Mitigating Worksheet"
    End If
    h = sourcedoc.Tables(1).Rows.Count - 1
    j = sourcedoc.Tables(1).Columns.Count
    ListBox1.ColumnCount = j
    ListBox1.ColumnWidths = "90;0"
    Dim myArray1() As Variant
    ReDim myArray1(h, j)
    For n = 0 To j - 1
        For m = 0 To h - 1
            Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
            myitem.End = myitem.End - 1
            myArray1(m, n) = myitem.Text
        Next m
    Next n
    ListBox1.List() = myArray1
    sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
    Application.ScreenUpdating = True

    For i = 0 To frmplan.lstTextBox3.ListCount - 1
        j = frmplan.lstTextBox3.List(i, 0)
        ListBox1.Selected(j) = True
    Next i
    End Select

    great !
    Sunday, September 14, 2014 7:56 PM