Posts by gollem

  • Combo into calender?

    Hi,


    you should use the Microsoft Date and time picker control.


    Take the visual basic toolbar(view-toolbars-visual basic) then click the hammer => you get the VB toolbar. Then click the hammer on the toolbar. Search the control I mentioned, click it and draw your box. This box does exactly what you want, you only have to program the code to put it in a cell.


    Gollem

  • Dependent Forms combobox

    Hi,


    I've added this module in your project to answer the first part of the question, the other part takes a lot of work.


    Private Sub Workbook_Open()
    Dim intCounter As Integer
    Dim strPrevious As String

    Sheet11.cboResources.Clear

    'Sort table
    Sheets("Resources").Select

    Cells.Select
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

    'Add data
    intCounter = 2
    strPrevious = ""
    Do While ActiveSheet.Cells(intCounter, 1).Value <> ""
    Select Case strPrevious = ActiveSheet.Cells(intCounter, 1).Value
    Case False
    Sheet11.cboResources.AddItem ActiveSheet.Cells(intCounter, 1).Value
    End Select

    strPrevious = ActiveSheet.Cells(intCounter, 1).Value
    intCounter = intCounter + 1
    Loop
    Sheets("Metrics").Select
    End Sub


    Gollem

  • If Statement Prob

    Ok Alexio, big problem. I'm out of solutions, the error you get is confirming what I've said before. The excel is not installed properly, how I know? I 've got the same error on my computer when I started to work.


    A solution to automatically set the name of a cell in the save as box when you click in excel save as without a button? I' have no idea...


    Gollem

  • How to call Excel from Visual Basic

    Hi,


    I've got 2 methods for you:


    Solution 1
    Just code for VB or Vba, try this(object method):


    Dim objExcel As Object

    Set objExcel = CreateObject("Excel.application")
    objExcel.Visible = True 'if you want to see excel during the program
    objExcel.Workbooks.open "....."


    .....


    objExcel.ActiveWorkbook.SaveAs "g:\tmp\test.xls"
    objExcel.ActiveWorkbook.Close
    objExcel.Quit
    Set objExcel = Nothing


    REMARK: if you run excel on the background and there is crash, you sometimes have to close the excel with the CTRL+DEL option(taskmanager)


    Solution 2
    First you have to select the Microsoft Excel (9.0) Object Library in the Project-reference menu


    Then you can use some code like below:

    Dim OExcel As Excel.Application
    Dim OWork As Excel.Workbook
    Dim OSheet As Excel.Worksheet

    Set OExcel = Excel.Application
    Set OWork = OExcel.Workbooks.Add
    Set OSheet = OWork.Worksheets.Add

    Osheet.Name = "test"
    Osheet.Cells(1, 1) = "test!"
    OWork.SaveAs "g:\tmp\test1.xls"
    OWork.Close
    OExcel.Quit

    Set OWork = Nothing
    Set OExcel = Nothing
    Set OSheet = Nothing


    This is the method I used the first time, now I'm using the object method.


    Your choice.


    Gollem

  • If Statement Prob

    Hi,


    normally you don't have to check it. What you can do is check it and you will notice that an icon appears on the toolbox. Now first select the form delete the current icon on the form and drag the new one(from the toolbox) to the form.


    Left click the icon: VIEW-PROPERTIES then name the dialogbox CommonDialog.


    Save the workbook and try again.


    If this doesn't work you have to give me the version of your commondialogbox and your excel(HELP-ABOUT microsoft excel).


    Gollem

  • Embedding an image/picture and changing it

    Hi Phooey,


    two ways to do it:


    First solution will mean that you have to run the macro for each picture.
    You use a cell on the sheet for example "A1", you put a 1 in it.


    Then you make this code:
    ActiveSheet.Pictures.Insert("c:\products\prod" & activesheet.range("A1").value &".jpg").Select


    At the end, before the end sub:


    activesheet.range("A1").value = activesheet.range("A1").value + 1 =>increment


    This means if you want to start over(if you want to begin again from picture 1), you have to fill in 1 in cell "A1"



    Solution2 with a loop: you have to run the macro 1 time
    add module:


    Public Sub Main()
    Dim intCounter As Integer

    intCounter = 1
    'Perform for picture 1 - 10
    Do While intCounter < 11
    Submit intCounter
    intCounter = intCounter + 1
    Loop
    End Sub
    => Adapt Submit


    public sub submit(intCounter as integer)


    ....


    ActiveSheet.Pictures.Insert("c:\products\prod" & intCounter & "#.jpg").Select


    .....


    end sub


    Hope this works for you.


    Gollem

  • If Statement Prob

    Hi,


    are you sure you have the full version of excel2000 installed, because if you still get that error it probably means that the commondialog-control(your save-as box) is not properly installed.


    You could check this if you want:


    Open your excel
    TOOLS-MACRO-VISUAL BASIC EDITOR
    You should get an explorer window(if not: VIEW-PROJECT EXPLORER)
    Then do as in attachement1(Picture1): Select the userform-click the hammer
    Then do as in attachement2(Picture2):TOOLS-ADDITIONAL CONTROLS => check if you could find the common dialog control(what version 6.0?)


    If it is not in the list, you can't use the example because the control isn't installed.


    Gollem

  • If Statement Prob

    Hi,


    are you sure you have the full version of excel2000 installed, because if you still get that error it probably means that the commondialog-control(your save-as box) is not properly installed.


    You could check this if you want: Open your excel

  • If Statement Prob

    Hi, see this thread: http://www.ozgrid.com/forum/showthread.php?t=22139



    Just do a if check before you let appear the box.
    Gollem

  • Combining Spreadsheets

    Hello,


    you can open every file in that directory(assuming that there are no other data-files) and after processing the data => sort your list. So processing every file regardless which date.


    for example you get a list:


    Date Data1 Data2
    01/05/2004 ... ...
    01/02/2004
    01/06/2004


    Gollem

  • Automating a graph range

    or solution with Vba(see attachement):


    Dim intTeller As Integer

    'see until where data is filled in
    intTeller = 2
    Do While ActiveSheet.Cells(intTeller, 2).Value <> ""
    intTeller = intTeller + 1
    Loop
    intTeller = intTeller - 1

    'define range of graph
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Values = "=Sheet1!R2C2:R" & intTeller& "C2"


    Gollem

  • Embedding an image/picture and changing it

    Hello,


    here is some code to insert and delete a picture:


    activeSheet.Pictures.Insert("Product1.jpg").Select
    ActiveSheet.Shapes(1).Select
    Selection.Delete


    To delete use 1 assuming that there will always be just 1 picture at the time on the sheet.


    If you want to some actions for all the pictures you should create a loop:


    dim strFile as string
    dim strDirectory as string 'directory where the pictures are ex.: c:\tmp\


    strFile = Dir(strDirectory & "*.jpg", vbNormal)
    Do While Not strFile = ""
    'Your actions ********


    'Delete picture
    ActiveSheet.Shapes(1).Select
    Selection.Delete

    'Insert next picture
    activeSheet.Pictures.Insert(strDirectory & "\" & strFile).Select

    strFile = Dir(strDirectory & "*.*", vbNormal)
    Loop
    Hope this helps you starting.


    Gollem

  • VLookup formula function

    Hello,


    the 2 means display the second column of the selected table, the 0 can also be a 1(True or false) If false the function searches an exact match if true(1) the function takes the first row that likes the most on the search value.


    => Vlookup searches a table on the most left value(the first column).


    For additional info use your help. (type Vlookup and you get detailed info)



    Gollem