Withusing of input box data store in given cell

D

deen

Hi Friends,

In sheet1 i need to given input data with using of input box,

EG: In sheet1 A1:A7, once open the excel input box have prompt as data
like below,

Data1 enter the value ____________
Data2 enter the value ____________
Data3 enter the value ____________
Data4 enter the value ____________

Once i enter the data click "OK" automatically data store in a1 to a4
based on input and rest of the cell will be blank( a5 to a7)

Thanks & Regards,
Deen
 
F

FSt1

hi
input boxes may not work like you invisioned. to do what you ask, in it's
simplest form, it might look like this.....
Sub deen()
Range("A1").Value = InputBox("enter a date")
Range("A2").Value = InputBox("enter a name")
Range("A3").Value = InputBox("enter a title")
Range("A4").Value = InputBox("enter somthing")
End Sub

4 seperate input boxes for 4 seperate datas. could be prettier.
and i not to sure if you users would like the way it works, this is filling
each cell 1 at a time as the input boxes popup
you may consider a form with 4 text boxes for input and a label for each
text box asking for the desired data and a commmand button to fill the cells
with the text box input. you would be able to review all the input before
filling the cells.
Private Sub CommandButton1_Click()
Range("A1").Value = textbox1.value
Range("A2").Value = textbox2.value
Range("A3").Value = textbox3.value
Range("A4").Value = textbox4.vlaue
end sub

post back if you have further questions.

regards
FSt1
 
P

Patrick Molloy

using a Userform would be much easier wouldn't it?
just put the 7 textboxes with 7 corresponding labels, and two buttons - one
cancel and one save
code the save button to push the data into the cells...

the code below is for a userform with just two buttons, btnSave and btnClose
The initialisation part of the code adds a number of labels and text boxes
....you can change this quite easily by chanmging the loop count


Option Explicit
Dim TP As Long
Dim index As Long

Public ctrl As Control
Private Sub UserForm_Initialize()
For index = 1 To 7
Add_A_Control
Next
End Sub
Private Sub Add_A_Control()
Set ctrl = Me.Controls.Add("Forms.Label.1")
With ctrl
TP = TP + ctrl.Height
.Top = TP
.Left = 25
.Caption = "A" & index & " value:"
End With
Set ctrl = Me.Controls.Add("Forms.Textbox.1")
With ctrl
.Top = TP
.Left = 75
.Tag = "A" & index
End With
End Sub


Private Sub btnSave_Click()
For Each ctrl In Me.Controls
If ctrl.Tag <> "" Then
Range(ctrl.Tag).Value = ctrl.text
End If
Next
End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub
 
D

Deen

Hi Patrick,

Now i'm facing new problem in user when ever open the excel, it shows error,
please help me on this

The error msg was,

runtime error 2147221005(800401f3)


coding is,
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("FOR").Select
Range("A1").Select
Load UserForm1
Application.ScreenUpdating = True
UserForm1.Show
End Sub


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Sheets("FOR").Select
Load UserForm1
Application.ScreenUpdating = True
UserForm1.Show
End Sub
 
P

Patrick Molloy

what does this do?

Load UserForm1
Application.ScreenUpdating = True
UserForm1.Show

all you need is

UserForm1.Show


more info on the error would be useful.

change this
Sheets("FOR").Select
so
WorkSheets("FOR").Activate
 
D

Deen

Hi patrick,

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
 
P

Patrick Molloy

you are using the form incorrectly

you have for example:

Private Sub CommandButton4_Click()
Worksheets("FOR").Activate
Range("C2").Value = InputBox("Enter Product Version")
End Sub

using an INPUTBOX on a userform should not be necessary as you just use the
textbox.

assuming you have a textbox, call it textbox4 (as we have button 4 click
event)

Private Sub CommandButton4_Click()
Worksheets("FOR").Range("C2").Value = textbox4.Text
End Sub

also, your form will have 8 textboxes...but you need only ONE button for OK
and that would enter all the textbox values to your sheets.

At what line does the code break?
 
D

Deen

Hi Patrick,

I hope you are doing well,

I have 1 more doubt, In my user form ihave the command button called browse
the data. once i click the button need to browse windows open to search the
..CSV once i select the particular csv file click open, Automatically data
will copy(import) and paste in to the master excel, in that worksheet name
called entity.

Could you please help me on this.


Thanks in advance

Deen
 
P

Patrick Molloy

here's the demo

on a userform
place three controls
textbox , name:= txtFileName
commandbutton, name:= btnBrowse, caption: Browse...
commandbutton, name:= btnOpen, caption:= Open

add this code:=

Option Explicit
Private Sub btnBrowse_Click()
Dim sPath As String
sPath = "C:\temp\" 'whatever you need
ChDir sPath
txtFileName = Application.GetOpenFilename("CSV Files (*.csv), *.csv)")
End Sub
Private Sub btnOpen_Click()
If txtFileName.Text = "" Then Exit Sub
If Dir(txtFileName.Text) = "" Then Exit Sub
FetchData
End Sub
Private Sub FetchData()
Dim wb As Workbook
Set wb = Workbooks.Open(txtFileName.Text)
wb.ActiveSheet.Cells.Copy ThisWorkbook.Worksheets("entity").Range("a1")
wb.Close False
End Sub


How it works...
user can either type in the full name into the text box or click the browse
button. when the browse button is clicked the full path and name of the
selected file is placed into the textbox
if the user is ok with this, press the Open button.
the csv file is opened and the activesheet copied to the worksheet called
entity
 
D

Deen

Hi Patrick

Its working great, I treat you. Thank you very much.

And patrick i have facing one more problem, See below in that command button
script is in userform command button, in entity sheet C&D column is have any
blank cell need to delete the entire row,

But is shows the error in that line "Selection.EntireRow.Delete"

Error like : Run time error '1004'
Cannot use that command on overlapping selections.


Private Sub CommandButton12_Click()
Worksheets("ENTITY").Activate
Columns("C:D").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub
 
P

Patrick Molloy

i don't have access to excel just now, but try this

Private Sub CommandButton12_Click()
With Worksheets("ENTITY")
.Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Columns("D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ending Procedure When Condition is Met Anywhere in Procedure 2
Counting the Number of Elements in an Array 3
Validate User Input in Dialog box 3
Default data in an input box 1
Dynamic link to workbook 3
Copy rows multiple times changing value of a cell in one column each time 1
input box prompt 1
repeat entry based on value in cell 6

Top