Posts by Craig Ottley

Craig Ottley · Mar 26th 2007

Re: Container Linking Data

it's not a frame just a tabstrip... i was thinking you were populating a list box for some reason...

the information in your tabstrip is populated by the values in the cells...

is there anything in particular you need the tabform to do?

Craig Ottley · Mar 26th 2007

Re: Navigation Buttons With Sub-menus

ok try something like this

Code

Sub insertCbo()


Set oleX = ActiveSheet


oleX.Shapes.AddOLEObject(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=100, Top:=20, Width:=240, Height:= _
        20).Select


Selection.ListFillRange = "B1:B10"


End Sub

Display More

to expand a bit more on what you can do with it look at this link http://www.ozgrid.com/Excel/add-values-combobox-excel.htm

hope this gets you started well

Craig Ottley · Mar 26th 2007

Re: Navigation Buttons With Sub-menus

would you want the combo box to be displayed directly on the worksheet or in a form?

Craig Ottley · Mar 26th 2007

Re: Container Linking Data

i think i know what you mean...

when you click on the tab strip it populates the control form with 'private' subs("hidden code in vba")... the form uses global directories to get the data it requires...

i sort of know how this has been done as i've created invoice batch systems using this method...

it's hard to explain but if you have any questions relating to this i can help you out

zimitry

Craig Ottley · Mar 26th 2007

Re: Input Data Into Option Buttons, Listbox, Text Box Etc In A Frame In Web Pages

from my understanding you can't do this in excel. it is possible in C#, C++, Visual studio etc...

this website will hopefully guide you in the right direction http://www.iopus.com/imacros/home/

zimitry

Craig Ottley · Mar 20th 2007

Re: Sql Query Syntax

this is probably too late but here it goes...

Code

strSQL = "SELECT tbl_MarkUp.[ProductCode], [Area], [AdminFee], [ValidFrom], [ValidTo], [MarkUp]" _
            & " FROM tbl_MarkUp" _
            & " WHERE [ProductCode] = [b][COLOR="Red"]'[/COLOR][/b]" & [Forms]![RackNetConv]![cbo_rProduct] & "[b][COLOR="Red"]'[/COLOR][/b]AND [ValidFrom] <= [b][COLOR="Red"]#[/COLOR][/b]" & [Forms]![RackNetConv]![cbo_rArrivalDate] & "[b][COLOR="Red"]#[/COLOR][/b] And [ValidTo] >= [b][COLOR="Red"]#[/COLOR][/b]" & [Forms]![RackNetConv]![cbo_rArrivalDate] & "[b][COLOR="Red"]#[/COLOR][/b];"

for some reason you have to put [COLOR="red"]'[/COLOR]before and after for text and [COLOR="Red"]#[/COLOR] for dates. numbers work fine

Craig Ottley · Mar 20th 2007

Re: Replace

please could you be more explicit?

Craig Ottley · Mar 20th 2007

Re: Sumif Function In Pivot Calculated Field?

writing a query would be much easier! time saving too...

Craig Ottley · Mar 20th 2007

Re: Sumif Function In Pivot Calculated Field?

i think this might help

Craig Ottley · Mar 20th 2007

Re: Application.run With Variable

this works for me...

Code

Const xDir As String = "\\NTCH05\"


Public Function RunObjlXL()
   
    Dim objXL As Object
    Dim wbRun As Object


    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set objXL = GetObject(, "Excel.Application")
        Set wbRun = objXL.Workbooks.Open(Filename:=xDir & "Variance_Pivots" & ".xls")
    
         Application.Run (wbRun.Name & "!Pts_Refresh")
   
     
    wbRun.Close True 'Save Chanegs = true
     
    Set objXL = Nothing
    Set wbRun = Nothing
    
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True


End Function

Display More

Craig Ottley · Mar 20th 2007

Re: Sumif Function In Pivot Calculated Field?

please could you attach a sample.xls?

Craig Ottley · Mar 20th 2007

Re: Set Print Range Command Button

something along the lines of this should work fine

Code

Sub Macro1()


Dim iPass As String


iPass = "PASSWORD"


ActiveSheet.Unprotect 'ipass


    Range("A2:B5").Select
    Selection.PrintOut copies:=1
   
ActiveSheet.Protect 'ipass




End Sub

Display More

Craig Ottley · Mar 20th 2007

Re: Sumif Function In Pivot Calculated Field?

try using =getpivotdata() that will work as sumif

Craig Ottley · Mar 20th 2007

Re: Detecting Page Breaks

ok i think this may do the trick for you...

you'll need to modify it to suite your needs but this should help

Code

Sub checkPageBreaks()
     
     ' Change Columns to which ones are actually hidden
      Dim iColumn, iNumber As Long
    
     
     On Error Resume Next
        iColumn = ActiveSheet.VPageBreaks(1).Location.Column
    
        If iColumn = 0 Then
            
            'Do Nothing
            
        ElseIf iColumn <= 17 And iColumn >= 1 Then
            
            On Error Resume Next
            
           Application.DisplayAlerts = False
                
          iNumber = Application.InputBox(prompt:="Please Select How Many Pages you want to fit", Title:="PageSetup", Type:=1)
               On Error GoTo 0
                Application.DisplayAlerts = True
                If iNumber = 0 Then
                    Exit Sub
                ElseIf iNumber = 1 Then
                    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
                    ElseIf iNumber = 2 Then
                       Set ActiveSheet.VPageBreaks(1).Location = Range("Q1")
                    End If
                 
             
              End If

Display More

Craig Ottley · Mar 20th 2007

Re: Detecting Page Breaks

this code will check your page breaks

Code

Sub checkPageBreaks()
     
     Dim iColumn As Long
     
     On Error Resume Next
        iColumn = ActiveSheet.VPageBreaks(1).Location.Column
    
        If iColumn = 0 Then
        
             MsgBox "No Vertical PageBreaks"


        ElseIf iColumn <= 17 And iColumn >= 1 Then
        
             MsgBox iColumn


        End If

Display More

this can be your first building block from here you should be able to set your page breaks as required?

hope this helps

Quote

Reply to your code

does your data always have a fixed amount of columns? (IE Fields)

Craig Ottley · Mar 20th 2007

Re: Detecting Page Breaks

Does your sheet always have page breaks in them or is the page only autopagebreaked when coumns are unhidden?

Craig Ottley · Mar 20th 2007

Re: Find Method Version 2000 Vs 2003

in excel 2003 there has been a change to the find function this is how the cells.find is supposed to work now

Code

Sub Macro1()


Dim sFind As String
sFind = 1
    
    Cells.Find(sFind, LookAt:= _
        xlPart, [b]SearchFormat:=False[/b]).Activate
        
End Sub

Searchformat has to be in the code for 2003 as i had problems when converting from 2000 to 2003 Office

This should solve your problem

zimitry

Craig Ottley · Mar 19th 2007

Re: Combobox Not Updating

just a stab in the dark. check your combo boxes to see if they have been embedded?

Code

Sub getOLEObejects()


Set NewSheet = Worksheets.Add
i = 2


NewSheet.Range("A1").Value = "Name"
NewSheet.Range("B1").Value = "Link Type"


For Each obj In Worksheets("Sheet1").OLEObjects
    
    
    NewSheet.Cells(i, 1).Value = obj.Name
    If obj.OLEType = xlOLELink Then
        NewSheet.Cells(i, 2) = "Linked"
    Else
        NewSheet.Cells(i, 2) = "Embedded"
    End If
    i = i + 1
Next obj




End Sub

Display More

Craig Ottley · Mar 19th 2007

Re: Calendar - Limiting Day Selection

use any of them just make sure you add cDate(calendar1.value) this converts the value into a date and will work on all formats IE yyyy-mm-dd, dd-mmm-yyy etc

Craig Ottley · Mar 19th 2007

Re: Calendar - Limiting Day Selection

your Welcome