Deen,
Add this to your userform code sheet, then step through the code with F8 and
notice at which line the error occure.
Then post back and tell us where your code fails, and someone will be able
to help you.
Private Sub UserForm_Initialize()
Stop
End Sub
Regards,
Per
"deen" <
[email protected]> skrev i meddelelsen
Hi,
When ever the open the excel sheet, Userform need to activate(open)
automatically that's what i done in the workbook code,
But it was shows error,
The error msg was,
runtime error 2147221005(800401f3)
invalid class string
error
Please help on this. i was try lot things but there is no solution,
Fyi, My entrie code was below.
In user form code :
Option Explicit
Dim TP As Long
Dim index As Long
Public ctrl As Control
Private Sub Add_A_Control()
Set ctrl = Me.Controls.Add("Forms.Label.1")
With ctrl
TP = TP + 30
..Top = TP + 20
..Left = 30
..BackColor = &H8000000D
..FontSize = 12
..ForeColor = &H8000000F
..Caption = "Entity" & index & ":"
End With
Set ctrl = Me.Controls.Add("Forms.Textbox.1")
With ctrl
..Top = TP + 20
..Left = 100
..Width = 130
..Tag = "A" & index
End With
End Sub
Private Sub CommandButton1_Click()
For Each ctrl In Me.Controls
If ctrl.Tag <> "" Then
Range(ctrl.Tag).Value = ctrl.Text
End If
Next
Sheets("face").Select
Range("A1").Select
Unload Me
End Sub
Private Sub CommandButton2_Click()
Worksheets("Face").Activate
Range("A1").Select
Unload Me
End Sub
Private Sub CommandButton3_Click()
Worksheets("FOR").Activate
Range("C1").Value = InputBox("Enter Pattern Number")
End Sub
Private Sub CommandButton4_Click()
Worksheets("FOR").Activate
Range("C2").Value = InputBox("Enter Product Version")
End Sub
Private Sub CommandButton5_Click()
Worksheets("FOR").Activate
Range("C3").Value = InputBox("Enter Scan Engin version")
End Sub
Private Sub CommandButton6_Click()
Worksheets("ENTITY").Activate
Rows("1:1").Select
Selection.ClearContents
Range("A1").Select
Worksheets("ENTITY").Activate
Range("A1").Select
ActiveCell.FormulaR1C1 = "Entity id"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Domain"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Machine Name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "IP Address"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Platform"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Product"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Product Version"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Pattern Number"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Scan Engin"
Range("A1").Select
Worksheets("ENTITY").Activate
Range("A1").Select
'ActiveWorkbook.Save'
Worksheets("ENTITY").Activate
Columns("B:U").Select
Selection.Insert Shift:=xlToRight
Selection.ColumnWidth = 2.14
Columns("A:A").Select
Selection.Copy
Application.CutCopyMode = False
Worksheets("ENTITY").Activate
Columns("A:A").Select
Selection.Copy
Worksheets("ENTITY").Activate
Columns("U:U").Select
ActiveSheet.paste
Application.CutCopyMode = False
Worksheets("ENTITY").Activate
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True,
OtherChar _
:="-", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Columns("B:B").Select
Worksheets("ENTITY").Activate
Columns("B:T").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Selection.End(xlToRight).Select
Range("H1:J1").Select
Worksheets("ENTITY").Activate
Range("H1:J1").Select
Selection.Copy
Range("K1").Select
Worksheets("ENTITY").Activate
Range("K1").Select
ActiveSheet.paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Product Version"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Product Version Status"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Pattern Number Status"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Scan Engin Status"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Ser"
Range("K2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC8="""",RC8="" "",RC8="" ""),""No Product Version
Found"",IF(RC8<FOR!R2C3,""Old Product Version"",""Current Product
Version""))"
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC9="""",RC9="" "",RC9="" ""),""No Pattern Found"",IF
(RC9<FOR!R1C3,""Old Pattern"",""Current Pattern""))"
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=IF(OR(RC10="""",RC10="" "",RC10="" ""),""No Engin Version
Found"",IF(RC10<FOR!R3C3,""Old Scan Engin"",""Current Scan Engin""))"
Range("N2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC4="""",RC5=""""),""DEL"",IF(ISNA(VLOOKUP(RC1,FOR!
R1C1:R10C2,2,0)),"""",VLOOKUP(RC1,FOR!R1C1:R10C2,2,0)))"
Range("K2:N2").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.paste
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Worksheets("ENTITY").Activate
Columns("K:K").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Rows("1:1").Select
Range("I1").Activate
Selection.AutoFilter
Range("F1").Select
Selection.End(xlToRight).Select
Range("N1").Select
Selection.AutoFilter Field:=14, Criteria1:="DEL"
Worksheets("ENTITY").Activate
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.End(xlToRight).Select
Range("O1").Select
Selection.AutoFilter Field:=14
Worksheets("ENTITY").Activate
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Worksheets("R1").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("R2").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("R3").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("R4").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("R5").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("R6").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("R7").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("R8").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("R9").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("R10").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("Old Product").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("Old Pattern").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("Old Scan Engin").Activate
Cells.Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("ENTITY").Activate
Selection.End(xlToRight).Select
Range("M1").Select
ActiveCell.FormulaR1C1 = "Ser"
Range("M1").Select
Selection.AutoFilter Field:=13, Criteria1:="1"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:L").Select
Selection.Copy
Worksheets("R1").Activate
ActiveSheet.paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Worksheets("ENTITY").Activate
Range("M1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=13, Criteria1:="2"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:L").Select
Selection.Copy
Worksheets("R2").Activate
ActiveSheet.paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Worksheets("ENTITY").Activate
Range("M1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=13, Criteria1:="3"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:L").Select
Selection.Copy
Worksheets("R3").Activate
Range("A1").Select
ActiveSheet.paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Worksheets("ENTITY").Activate
Range("M1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=13, Criteria1:="4"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:L").Select
Selection.Copy
Worksheets("R4").Activate
ActiveSheet.paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Worksheets("ENTITY").Activate
Range("M1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=13, Criteria1:="5"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:L").Select
Selection.Copy
Worksheets("R5").Activate
ActiveSheet.paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Worksheets("ENTITY").Activate
Range("M1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=13, Criteria1:="6"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:L").Select
Selection.Copy
Worksheets("R6").Activate
Range("A1").Select
ActiveSheet.paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Worksheets("ENTITY").Activate
Range("M1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=13, Criteria1:="7"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:L").Select
Selection.Copy
Worksheets("R7").Activate
Range("A1").Select
ActiveSheet.paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Worksheets("ENTITY").Activate
Range("M1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=13, Criteria1:="8"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:L").Select
Selection.Copy
Worksheets("R8").Activate
Range("A1").Select
ActiveSheet.paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Worksheets("ENTITY").Activate
Range("M1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=13, Criteria1:="9"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:L").Select
Selection.Copy
Worksheets("R9").Activate
Range("A1").Select
ActiveSheet.paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Worksheets("ENTITY").Activate
Range("M1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=13, Criteria1:="10"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Columns("A:L").Select
Selection.Copy
Worksheets("R10").Activate
ActiveSheet.paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Worksheets("ENTITY").Activate
Range("M1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=13
Columns("M:M").Select
Selection.Delete Shift:=xlToLeft
Range("J1").Select
Selection.AutoFilter Field:=10, Criteria1:="Old Product Version"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Worksheets("Old Product").Activate
Range("A1").Select
ActiveSheet.paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Worksheets("ENTITY").Activate
Application.CutCopyMode = False
Range("J1").Select
Selection.AutoFilter Field:=10
Range("K1").Select
Selection.AutoFilter Field:=11, Criteria1:="Old Pattern"
Columns("A:A").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Worksheets("Old Pattern").Activate
Range("A1").Select
ActiveSheet.paste
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
Worksheets("ENTITY").Activate
Range("K1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=11
Selection.AutoFilter Field:=12, Criteria1:="Old Scan Engin"
Columns("A:A").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Worksheets("Old Scan Engin").Activate
Range("A1").Select
ActiveSheet.paste
Range("A1").Select
Worksheets("ENTITY").Activate
Range("L1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=12
Range("A1").Select
Worksheets("ENTITY").Activate
Rows("1:1").Select
Selection.AutoFilter
Range("A1").Select
Worksheets("FACE").Activate
Range("C14").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R1'!R2C10:R65536C10,""Current Product Version"")"
Range("C15").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R2'!R2C10:R65536C10,""Current Product Version"")"
Range("C16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R3'!R2C10:R65536C10,""Current Product Version"")"
Range("C17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R4'!R2C10:R65536C10,""Current Product Version"")"
Range("C18").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R5'!R2C10:R65536C10,""Current Product Version"")"
Range("C19").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R6'!R2C10:R65536C10,""Current Product Version"")"
Range("C20").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R7'!R2C10:R65536C10,""Current Product Version"")"
Range("C21").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R8'!R2C10:R65536C10,""Current Product Version"")"
Range("C22").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R9'!R2C10:R65536C10,""Current Product Version"")"
Range("C23").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R10'!R2C10:R65536C10,""Current Product Version"")"
Range("D14").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R1'!R2C10:R65536C10,""Old Product Version"")"
Range("D15").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R2'!R2C10:R65536C10,""Old Product Version"")"
Range("D16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R3'!R2C10:R65536C10,""Old Product Version"")"
Range("D17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R4'!R2C10:R65536C10,""Old Product Version"")"
Range("D18").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R5'!R2C10:R65536C10,""Old Product Version"")"
Range("D19").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R6'!R2C10:R65536C10,""Old Product Version"")"
Range("D20").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R7'!R2C10:R65536C10,""Old Product Version"")"
Range("D21").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R8'!R2C10:R65536C10,""Old Product Version"")"
Range("D22").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R9'!R2C10:R65536C10,""Old Product Version"")"
Range("D23").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R10'!R2C10:R65536C10,""Old Product Version"")"
Range("E14").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R1'!R2C10:R65536C10,""No Product Version Found"")"
Range("E15").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R2'!R2C10:R65536C10,""No Product Version Found"")"
Range("E16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R3'!R2C10:R65536C10,""No Product Version Found"")"
Range("E17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R4'!R2C10:R65536C10,""No Product Version Found"")"
Range("E18").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R5'!R2C10:R65536C10,""No Product Version Found"")"
Range("E19").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R6'!R2C10:R65536C10,""No Product Version Found"")"
Range("E20").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R7'!R2C10:R65536C10,""No Product Version Found"")"
Range("E21").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R8'!R2C10:R65536C10,""No Product Version Found"")"
Range("E22").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R9'!R2C10:R65536C10,""No Product Version Found"")"
Range("E23").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R10'!R2C10:R65536C10,""No Product Version Found"")"
Range("F14").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R1'!R2C11:R65536C11,""Current Pattern"")"
Range("F15").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R2'!R2C11:R65536C11,""Current Pattern"")"
Range("F16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R3'!R2C11:R65536C11,""Current Pattern"")"
Range("F17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R4'!R2C11:R65536C11,""Current Pattern"")"
Range("F18").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R5'!R2C11:R65536C11,""Current Pattern"")"
Range("F19").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R6'!R2C11:R65536C11,""Current Pattern"")"
Range("F20").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R7'!R2C11:R65536C11,""Current Pattern"")"
Range("F21").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R8'!R2C11:R65536C11,""Current Pattern"")"
Range("F22").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R9'!R2C11:R65536C11,""Current Pattern"")"
Range("F23").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R10'!R2C11:R65536C11,""Current Pattern"")"
Range("G14").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R1'!R2C11:R65536C11,""Old Pattern"")"
Range("G15").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R2'!R2C11:R65536C11,""Old Pattern"")"
Range("G16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R3'!R2C11:R65536C11,""Old Pattern"")"
Range("G17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R4'!R2C11:R65536C11,""Old Pattern"")"
Range("G18").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R5'!R2C11:R65536C11,""Old Pattern"")"
Range("G19").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R6'!R2C11:R65536C11,""Old Pattern"")"
Range("G20").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R7'!R2C11:R65536C11,""Old Pattern"")"
Range("G21").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R8'!R2C11:R65536C11,""Old Pattern"")"
Range("G22").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R9'!R2C11:R65536C11,""Old Pattern"")"
Range("G23").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R10'!R2C11:R65536C11,""Old Pattern"")"
Range("H14").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R1'!R2C11:R65536C11,""No Pattern Found"")"
Range("H15").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R2'!R2C11:R65536C11,""No Pattern Found"")"
Range("H16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R3'!R2C11:R65536C11,""No Pattern Found"")"
Range("H17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R4'!R2C11:R65536C11,""No Pattern Found"")"
Range("H18").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R5'!R2C11:R65536C11,""No Pattern Found"")"
Range("H19").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R6'!R2C11:R65536C11,""No Pattern Found"")"
Range("H20").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R7'!R2C11:R65536C11,""No Pattern Found"")"
Range("H21").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R8'!R2C11:R65536C11,""No Pattern Found"")"
Range("H22").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R9'!R2C11:R65536C11,""No Pattern Found"")"
Range("H23").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R10'!R2C11:R65536C11,""No Pattern Found"")"
Range("I14").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R1'!R2C12:R65536C12,""Current Scan Engin"")"
Range("I15").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R2'!R2C12:R65536C12,""Current Scan Engin"")"
Range("I16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R3'!R2C12:R65536C12,""Current Scan Engin"")"
Range("I17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R4'!R2C12:R65536C12,""Current Scan Engin"")"
Range("I18").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R5'!R2C12:R65536C12,""Current Scan Engin"")"
Range("I19").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R6'!R2C12:R65536C12,""Current Scan Engin"")"
Range("I20").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R7'!R2C12:R65536C12,""Current Scan Engin"")"
Range("I21").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R8'!R2C12:R65536C12,""Current Scan Engin"")"
Range("I22").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R9'!R2C12:R65536C12,""Current Scan Engin"")"
Range("I23").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R10'!R2C12:R65536C12,""Current Scan Engin"")"
Range("J14").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R1'!R2C12:R65536C12,""Old Scan Engin"")"
Range("J15").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R2'!R2C12:R65536C12,""Old Scan Engin"")"
Range("J16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R3'!R2C12:R65536C12,""Old Scan Engin"")"
Range("J17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R4'!R2C12:R65536C12,""Old Scan Engin"")"
Range("J18").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R5'!R2C12:R65536C12,""Old Scan Engin"")"
Range("J19").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R6'!R2C12:R65536C12,""Old Scan Engin"")"
Range("J20").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R7'!R2C12:R65536C12,""Old Scan Engin"")"
Range("J21").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R8'!R2C12:R65536C12,""Old Scan Engin"")"
Range("J22").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R9'!R2C12:R65536C12,""Old Scan Engin"")"
Range("J23").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R10'!R2C12:R65536C12,""Old Scan Engin"")"
Range("K14").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R1'!R2C12:R65536C12,""No Engin Version Found"")"
Range("K15").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R2'!R2C12:R65536C12,""No Engin Version Found"")"
Range("K16").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R3'!R2C12:R65536C12,""No Engin Version Found"")"
Range("K17").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R4'!R2C12:R65536C12,""No Engin Version Found"")"
Range("K18").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R5'!R2C12:R65536C12,""No Engin Version Found"")"
Range("K19").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R6'!R2C12:R65536C12,""No Engin Version Found"")"
Range("K20").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R7'!R2C12:R65536C12,""No Engin Version Found"")"
Range("K21").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R8'!R2C12:R65536C12,""No Engin Version Found"")"
Range("K22").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R9'!R2C12:R65536C12,""No Engin Version Found"")"
Range("K23").Select
ActiveCell.FormulaR1C1 = _
"=COUNTIF('R10'!R2C12:R65536C12,""No Engin Version Found"")"
Worksheets("ENTITY").Activate
Columns("J:L").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Worksheets("FACE").Activate
ActiveWindow.SmallScroll ToRight:=-3
Range("A1").Select
Unload Me
'ActiveWorkbook.Save'
End Sub
Private Sub CommandButton7_Click()
Worksheets("ENTITY").Activate
Rows("2:2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Worksheets("ENTITY").Activate
Rows("1:1").Select
Selection.ClearContents
Range("A1").Select
Worksheets("ENTITY").Activate
Range("A1").Select
ActiveCell.FormulaR1C1 = "Entity id"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Domain"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Machine Name"
Range("D1").Select
ActiveCell.FormulaR1C1 = "IP Address"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Platform"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Product"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Product Version"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Pattern Number"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Scan Engin"
Range("A1").Select
Worksheets("FOR").Activate
Range("A1:A10").Select
Selection.ClearContents
Range("C1:C3").Select
Selection.ClearContents
Range("A1").Select
Worksheets("FACE").Activate
Range("A1").Select
Unload Me
End Sub
Private Sub CommandButton8_Click()
On Error Resume Next
Worksheets("ENTITY").Activate
Range("A1").Select
Unload Me
On Error GoTo 0
End Sub
Private Sub UserForm_Initialize()
Worksheets("FOR").Activate
For index = 1 To 10
Add_A_Control
Next
End Sub
Private Sub Userform_Activate()
Label1.Caption = Format(Now, "mm/dd/yyyy hh:mm")
End Sub
In that workbook the short key code for userform enable:
Sub Shortkey()
'
' Shortkey Macro
' Macro recorded 6/12/2009 by Ahamed
'
' Keyboard Shortcut: Ctrl+i
'
Worksheets("FOR").Activate
Range("A1").Select
UserForm1.Show
End Sub
In face sheet i have 2 command button,:
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub
Private Sub CommandButton2_Click()
On Error Resume Next
Worksheets("ENTITY").Activate
Range("A1").Select
On Error GoTo 0
End Sub
In workbook code:
Private Sub Workbook_Open()
Worksheets("FOR").Activate
Range("A1").Select
UserForm1.Show
End Sub
Thanks in advance,
Deen
Barb,
This was also posted in another (worksheet functions) group and that's
pretty much what I recommended there. I suspect the error is within the
code
of the UserForm - typically those are detected during the
opening/initialization of the user form but they are reported without the
user form being visible. I recommended that the OP put a Stop command in
the
user form's .Initialize event and use [F8] to step through it's
code/opening
to identify the error location - which is pretty much exactly what you
recommend here. (Great Minds, and all like that!).
Jerry Latham
Barb Reinhardt said:
Why don't you step through your code and tell us exactly where it has an
error.
