Visit the Forums
Register Now!

User Login


Forum Topic

excel macro programming

  • if A1 is blank or with any other text, B1 can be blank.
    however, if A1 = "closed", B1 should have a text.

    I don't think this can be solved by data validation. Data validation will only work if there is data entered, so that the data can
    be validated.

    But this can easily be solved by some excel vba.

  • I don't think this can be solved by data validation. Data validation will only work if there is data entered, so that the data can
    be validated.

    But this can easily be solved by some excel vba.
    i was thinking the same but i can't seem to find a solution. any idea?

    thanks!
  • Goto vba editor double click on thisworkbook to access the workbook and select sheetChange.
    Use the following code.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Sheets("sheet1").Select
    If Range("a1").Value = "closed" And Range("b1").Value = "" Then
    MsgBox "Please enter a value in cell B1"
    End If

    End Sub


    If B1 is empty and value in a1 is "closed" and sheet1 is changed (like changing other value in a cell or emptying the b1 cell) then that msgbox will appear




    -- edited by ram2010 on Dec 07 2015, 09:28 PM
  • @ram2010

    thanks.. YGPM
  • dotdll Send Message View User Items on November 18, 2015 09:56 AM #
    @ botsblitz32 and ram2010

    Bali ganito po ang scenario ito sample: bali yung x and y columns are their coordinates.

    This can be solved by excel vba of course. Are you still interested on the solution?
  • Mga master need help..
    Need ko print laman Ng text box ko. What I have now is print Sa form. But need ko Sana Yung mismong laman Ng text box gusto ko print upon click Ng Isang command button.

    Thanks in advance.
  • up
  • Zendrix Send Message View User Items on December 24, 2015 04:34 PM #

    I tried to create a form put a textbox and place a text in the textbox.
    I am able to print the text in the textbox.

    Is there any other details of your problem?
  • sir isa pa po..

    data like this.

    date name item
    1/1/16
    1/1/16
    1/1/16
    1/2/16
    1/2/16
    1/3/16
    1/3/16
    1/3/16


    what i want is to type like 1/3/16 in cell a1 then
    upon click a command button all data dated equal to a1 will be selected and
    transfer into another sheet.
  • Do like this.
    Create a button with a link to a macro.
    Your macro will scan column "a".
    The macro will record the value in a1 as its fixed value. When scanning a2, a3 ... compare it with the fixed value.
    If the same, then record the whole row to another sheet. Continue scanning column "a" exit when done.
  • Hi sir ram2010, I need help

    need ko mag karoon ng isang button para sa search engine means pag ng search ka lahat ng laman ng sheet tab masearch mo kung andun nga ung name nya, di lang po isang sheet tab, sample sheet tab 1/sheet tab 2/sheet tab 3/
    isang search engine lang kukunin na agad ung file na nakita nya..

    pano po ba gagawin dun sir my sample ka po sir ram. thanks po
  • Sir Ram Sample code nga po nung scanning?
  • Mga master ng vba,

    Patulong nman... I want to paste some pictures in chronological order ng file name na 1 to 45 na picture to a certain cell sa excel na automatic din magresize sa size ng specific cell. Possible kaya ito... file is located in a certain folder... sana may makatulong guys
  • Zendrix Send Message View User Items on January 27, 2016 04:05 PM #
    Sir Ram Sample code nga po nung scanning?


    Here is the sample code.

    Sub SearchAndCopy()
    'Search the value in a1 along column 'A' starting at a2
    'in sheet1 and copy the entire row to sheet2.
    'Add the word End at the end of column 'A' to terminate the scan.

    Dim destRng As Range
    Dim valueToSearch As Range
    Dim SrchRng As Range

    Set valueToSearch = Sheets("Sheet1").Range("A1") 'User input value
    Set destRng = Sheets("Sheet2").Range("A1") 'Start point of copied rows or destination
    Set SrchRng = Sheets("Sheet1").Range("A2") 'Start point of search

    Do While SrchRng.Value <> "End" 'Exit the search if value of cell is End
    If SrchRng.Value = valueToSearch Then
    destRng.EntireRow.Value = SrchRng.EntireRow.Value 'Copy the entire row to sheet2
    Set destRng = destRng.Offset(1, 0) 'Update destRng for next copy location
    Set SrchRng = SrchRng.Offset(1, 0) 'Update SrchRng to search the next range
    Else
    Set SrchRng = SrchRng.Offset(1, 0) 'Update SrchRng to search the next range
    End If
    Loop

    End Sub


    Sample data in sheet1
    1/29/2016 Name Item
    1/29/2016 Lego 2
    1/29/2016 Medicine 4
    1/28/2016 PC 7
    1/28/2016 Lantern 8
    1/27/2016 Chair 96
    1/26/2016 Pencil 14
    1/25/2016 Filters 87
    End


    Sample output in sheet2.
    1/28/2016 PC 7
    1/28/2016 Lantern 8
  • fmstatic14 Send Message View User Items on January 27, 2016 01:40 PM #
    Hi sir ram2010, I need help

    need ko mag karoon ng isang button para sa search engine means pag ng search ka lahat ng laman ng sheet tab masearch mo kung andun nga ung name nya, di lang po isang sheet tab, sample sheet tab 1/sheet tab 2/sheet tab 3/
    isang search engine lang kukunin na agad ung file na nakita nya..

    pano po ba gagawin dun sir my sample ka po sir ram. thanks po


    This is not clear to me.
    Ano itong,
    means pag ng search ka lahat ng laman ng sheet tab masearch mo


    Tapos at the end,
    isang search engine lang kukunin na agad ung file na nakita nya..


    Ano itong laman? at ano itong file na nakita nya?
  • wow try ko to sir ram...
    thanks po
  • me way ba na paginamitan ko ng barcode scanner yung spreadsheet ko nang inventory e hahanapin nya sa lahat ng sheet yung serial number na nascan ng barcode scanner?

    ang nangyayari kasi once na tinapat lang sa isang shell ipprovide nya lang yung serial na nakita nya.

    sana po me makatulong.

    Sir Ram2010 eto na naman ako baka matulungan mo ulit ako.

    salamat,
  • t@djhrt

    Sir try mo vlookup. let say sa A1 yung serial came from your barcode then at b1 mo ilagay yung formula to search for the value you need in other sheet.
  • masters question

    can a newbie like me who only knows ONLY vlookup have a chance to learn his way around macro programming? naiingit kasi ako sa mga officemate ko na kayang magwork their way around excel automation, parang Gandalf ang tingin sa kanila sa office.
  • @Zendrix

    sir pwede mo kong gawan ng code di ko ganung gets eh!
    eto kasi yung nakuha kong code sa net at inedit ko na lang, pagkasi magscan dapat irun muna yung macro then saka itatapat ang barcode scanner sa barcode nung ichecheck na serial number sa spreadsheets.

    Sub DataInput()
    '
    'Copyright(c) 2005-2006 Serialio.com
    '
    'This prompts for an input, then does a lookup for the data in the specified column
    'If the data is found, it puts a timestamp in the cell to the right of the data
    'If the data is not found it shows a dialog indicating that
    'Assumption is that entries in the searched column are unique

    'sVal = InputBox("Scan or type product barcode...", "LaserChamp Input")
    'ActiveCell.Value = sVal

    Dim SearchTarget As String
    Dim myRow As Long
    Dim Rng As Range
    Static PrevCell As Range
    Dim FoundCell As Range
    Dim CurCell As Range

    'SearchTarget = "asdf"
    SearchTarget = InputBox("Scan or type product barcode...", "LaserChamp Input")

    If PrevCell Is Nothing Then
    myRow = Selection.Row
    Set PrevCell = Range("G" & myRow)
    End If

    'Set Rng = Range("G:G,G:G") 'Columns for search defined here
    Set Rng = Range("G:G,G:G") 'Columns for search defined here
    With Rng
    Set FoundCell = .Cells.Find(What:=SearchTarget, _
    After:=PrevCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False)
    End With

    If FoundCell Is Nothing Then
    MsgBox SearchTarget & " - This item you are looking for is not listed yet."
    Else
    FoundCell.Activate
    ' If PrevCell.Address = FoundCell.Address Then
    ' MsgBox "there's only one!"
    ' End If
    ActiveCell.Offset(0, 4).Select
    timestamp = Format(Now(), "dd-mmm-yyyy")
    ActiveCell = timestamp
    ActiveCell = Now()

    Set PrevCell = FoundCell
    End If
    End Sub
  • sana me makatulong bale po jan sa code na yan naghahanap lang sya na G na column jan kasi nakalagay yung existing na serial number ng mga IT Equipment na iniscan ko, so pagnagscan ako ng serial na me barcode maghahanap sana sya sa buong worksheet na ginawa ko, nasa 30 sheet na tab yung paghahanapan nya bale ibat ibang department kasi yung mga sheet tab na yun.

    baka me mas madali kayong code jan pra magsearch sya sa lahat ng sheet.
  • hi sir ram.. im done na po ako dun sa una ko tanong.. ehehe pinadali ko na lang..

    eto po sir ram..i need lang po inventory


    pano po ba mag create ng formula para sa pag baril ng barcode

    once na may dumating na delivery type ko lang po ung item tapos baril ng barcode nya need nya pumasok sa data para mag add ng qty?

    tapos pag my bumili, search lang ung item na binili, tapos baril ulit ng barcode nya mg negative naman po xia ulit sa data base inventory..

    sir ram help naman po pag mga damitan lang po to like shoes thanks sir ram
  • Hello po, I would like to ask if anyone knows or recommend a school that teaches advanced excel? During weekdays po sana..
  • is there any way makapag-auto trace sa line chart using vba?

    example: Meron isang line chart na picture....using vba.. pede madetect ung possible values nung mga line dun sa picture para maconvert as data para sa chart?.. possible kaya? or medyo complex na to?
  • djhurt Send Message View User Items on 15 Feb 16 @ 07:11 PM #
    me way ba na paginamitan ko ng barcode scanner yung spreadsheet ko nang inventory e hahanapin nya sa lahat ng sheet yung serial number na nascan ng barcode scanner?

    ang nangyayari kasi once na tinapat lang sa isang shell ipprovide nya lang yung serial na nakita nya.

    sana po me makatulong.

    Sir Ram2010 eto na naman ako baka matulungan mo ulit ako.

    salamat,


    I am not familiar with barcode scanner in terms of automatic input to excel. Perhaps once the scan is done, a macro is run, so that the code that was read is automatically saved to a sheet. If this is so then we need to find that macro name, revise it and make it work to whatever we want it to do. Searching an item on all sheets in a workbook is not that difficult.
  • froztburn Send Message View User Items on 19 Feb 16 @ 01:43 PM #
    masters question

    can a newbie like me who only knows ONLY vlookup have a chance to learn his way around macro programming? naiingit kasi ako sa mga officemate ko na kayang magwork their way around excel automation, parang Gandalf ang tingin sa kanila sa office.

    Programming is mainly only about your interest and time to practice it.

    -- edited by ram2010 on Mar 23 2016, 06:09 PM
  • fmstatic14 Send Message View User Items on 03 Mar 16 @ 03:22 PM #
    hi sir ram.. im done na po ako dun sa una ko tanong.. ehehe pinadali ko na lang..

    eto po sir ram..i need lang po inventory


    pano po ba mag create ng formula para sa pag baril ng barcode

    once na may dumating na delivery type ko lang po ung item tapos baril ng barcode nya need nya pumasok sa data para mag add ng qty?

    tapos pag my bumili, search lang ung item na binili, tapos baril ulit ng barcode nya mg negative naman po xia ulit sa data base inventory..

    sir ram help naman po pag mga damitan lang po to like shoes thanks sir ram

    I don't know much about barcode scanner. I think you need to ask the supplier of your barcode scanner. They might have a manual on how to do it.
  • bongpagong Send Message View User Items on 16 Mar 16 @ 02:00 PM #
    Hello po, I would like to ask if anyone knows or recommend a school that teaches advanced excel? During weekdays po sana..

    I think for advance topics, there are lot you can find in online-courses, with fee and without fee.

    Are you after the diploma or just to improve your skill and knowledge?
  • jazzantriah Send Message View User Items on 17 Mar 16 @ 12:14 AM #
    is there any way makapag-auto trace sa line chart using vba?

    example: Meron isang line chart na picture....using vba.. pede madetect ung possible values nung mga line dun sa picture para maconvert as data para sa chart?.. possible kaya? or medyo complex na to?

    Yes this is possible as long as that line chart picture is a normal excel chart.
  • pano po ba mag create ng formula para sa pag baril ng barcode

    once na may dumating na delivery type ko lang po ung item tapos baril ng barcode nya need nya pumasok sa data para mag add ng qty?

    tapos pag my bumili, search lang ung item na binili, tapos baril ulit ng barcode nya mg negative naman po xia ulit sa data base inventory..

    sir ram help naman po pag mga damitan lang po to like shoes thanks sir ram


    sir what do you mean with this... using barcode reader sya yung magsasabi or mag input nung QTY to add and less to your inventory?

    -- edited by Zendrix on Apr 02 2016, 09:30 AM

    -- edited by Zendrix on Apr 02 2016, 09:31 AM