enhance add ins excel,using VB

[faizshafie]

hi there
i need help
i have a project
maybe it need to use VB but i dont really know how to program it
basic ideas are:

i need to autamated the excel files. an add ins
in this add in it can trigger my data acquisition to stat my measurement.
but formerly,it just use manually.click when we need..it repeatative.
so when we can autamated it it save our energy to click..

recently i try to use macros recording then i want to see the VB cding..then when i run it again,it fail. my friend said that i dont declare the variable..
but i dont know what variable i need to declare.

i just need a start button to manage the start button in the excel add in sheet..

or in other words:
It sounds like you need a VBA based addin with a button that when pressed will invoke your software to capture the data you are after and then import it into Excel after the program finishes.

how i need to do that?

 

[mole999]

post the code you recorded between code tags

[ code ] *** [ /code] without the spaces

code does fail because it only records what you do, not what you want to happen

 

[faizshafie]

Sub try()
'
' try Macro
' help me to adjust this to be an autamated excel
'
'
Range("C11:D11").Select
IgnoreBlank = Range("C11")
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Record,Start,Trigger,Stop,Reset,Clear"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Range("C19:D19").Select
IgnoreBlank = Range("C11")
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Record,Start,Trigger,Stop,Reset,Clear"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
ActiveWorkbook.Worksheets("Meter Scan").Names.Add Name:= _
"'Meter Scan'!TaskStatusCmds0_DisplayStyle", RefersToR1C1:="=1"
IgnoreBlank = Range("C19")
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="INF,{value}"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
ActiveCell.FormulaR1C1 = "10"
Range("C13:D13").Select
IgnoreBlank = Range("C19")
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="INF,{value}"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
ActiveWorkbook.Worksheets("Meter Scan").Names.Add Name:= _
"'Meter Scan'!SampleTriggerCount0_DisplayStyle", RefersToR1C1:="=2"
IgnoreBlank = Range("C13")
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Run Once,Run Continuously"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
Range("C24:D24").Select
IgnoreBlank = Range("C13")
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Run Once,Run Continuously"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
ActiveWorkbook.Worksheets("Meter Scan").Names.Add Name:= _
"'Meter Scan'!ArmTriggerScanMode0_DisplayStyle", RefersToR1C1:="=1"
IgnoreBlank = Range("C24")
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Meter Scan,Sheet1,Sheet2,Sheet3"
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
End Sub

 

[mole999]

This works, though I have no idea what its supposed to do

Sub try()
'
' try Macro
' help me to adjust this to be an autamated excel
'
'
    Range("C11:D11").Select
    IgnoreBlank = Range("C11")
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="Record,Start,Trigger,Stop,Reset,Clear"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    
     Range("C13:D13").Select
    IgnoreBlank = Range("C19")
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="INF,{value}"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    ActiveWorkbook.Worksheets("Meter Scan").Names.Add Name:= _
                                                      "'Meter Scan'!SampleTriggerCount0_DisplayStyle", RefersToR1C1:="=2"
    IgnoreBlank = Range("C13")
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="Run Once,Run Continuously"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    
    Range("C19:D19").Select
    IgnoreBlank = Range("C11")
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="Record,Start,Trigger,Stop,Reset,Clear"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    ActiveWorkbook.Worksheets("Meter Scan").Names.Add Name:= _
                                                      "'Meter Scan'!TaskStatusCmds0_DisplayStyle", RefersToR1C1:="=1"
    IgnoreBlank = Range("C19")
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="INF,{value}"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    ActiveCell.FormulaR1C1 = "10"
       
    Range("C24:D24").Select
    IgnoreBlank = Range("C13")
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="Run Once,Run Continuously"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    ActiveWorkbook.Worksheets("Meter Scan").Names.Add Name:= _
                                                      "'Meter Scan'!ArmTriggerScanMode0_DisplayStyle", RefersToR1C1:="=1"
    IgnoreBlank = Range("C24")
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="Meter Scan,Sheet1,Sheet2,Sheet3"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With

End Sub

 

[faizshafie]

are you sure?
i try it but it dont work.error ;(

 

[faizshafie]

can i have your email fo easier to contact

 

[mole999]

the code I posted was in a blank 2007 work book (sheet 1) with three sheets.

i was on sheet one and i named sheet two as Master Scan

I then stepped through the macro with F8, line by line. Running the code also works as stands.

So the code runs, so it has to be related to the data or its format that is picked up by the code,

what line fails (yellow) for debug, any error messages

what is being read, is it text or numbers

 

[faizshafie]

WOW. really that the coding make sense? can run? ouch how come i dont get it.

erm.. Error in ths line

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Record,Start,Trigger,Stop,Reset,Clear"

 

[mole999]

Ok, if I run the code from Meter Scan as the main page selected I ave an error

If I run it from sheet 1, the work is applied to sheet 1

is that as intended

if i run it from any sheet that dosent have the code in its objects page i get a 1400 error

this is confusing

    Range("C19:D19").Select
    IgnoreBlank = Range("C11")
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="Record,Start,Trigger,Stop,Reset,Clear"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    ActiveWorkbook.Worksheets("Meter Scan").Names.Add Name:= _
                                                      "'Meter Scan'!TaskStatusCmds0_DisplayStyle", RefersToR1C1:="=1"
    IgnoreBlank = Range("C19")
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
             xlBetween, Formula1:="INF,{value}"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    ActiveCell.FormulaR1C1 = "10"

it creates the drop downs, and then overwrites the drop down with a text 10 in C19

 

[faizshafie]

i will try..