Continuous Form vba Format

[aron.ridgway]

I have a continuous form that has several buttons, i am hiding them depending on which buttons they press before hand.

Is there a way to hide the spaces left by the invisible buttons using vba? or is there a simpler way?

thanks

 

[CJ_London]

why not disable the buttons rather than hiding them?

 

[aron.ridgway]

There are quite a few buttons so i wanted to conserve space as the form is already quite wide?

 

[CJ_London]

OK, you can use vba code to move buttons around. Where you put the code will be down to you but will typically in the same event that you are using to hide the buttons at present.

Normally you would give buttons meaningful names but in this instance it is probably better to call them btn1, btn2 etc to simplify the amount of code.

So you might have some code along the following lines

dim i as integer
dim l as single
 
'code here to make buttons visible or not
 
l=60 'twips from edge
For i=1 to 10
    if me("btn" & i).visible=true then
        me("btn" & i).left=l
        l=l+me("btn" & i).width+60
    end if
next i

 

[aron.ridgway]

Thank you for the reply, i will try and use the code.

could you explain briefly what the code is actually doing? thanks

 

[CJ_London]

each control has 4 'position' properties, top/left/width/height. The code is simply changing the left position for each control based on the widths of each 'previous' visible control. The 60 is just a spacer so that the controls have a small gap between them

 

[aron.ridgway]

ok i understand, i have five buttons which are called, cmdEditView, cmdReceive, cmdAmmendR, cmdRecInv, cmdAmmendI. how can i merge your code to fit with my buttons? as they are not called btn1 etc..?

 

[CJ_London]

you'll need more complex code - to start you off

dim i as integer
dim l as single

'code here to make buttons visible or not

l=60 'twips from edge
if cmdEditView.visible=true then
    cmdeditview.left=l
    l=l+cmdEditView.width+60
end if
if cmdReceive.visible=true then
    cmdReceive.left=l
    l=l+cmdReceive.width+60
end if
etc

 

[aron.ridgway]

thank you i have got the following, i have just tried the buttons are staying in the same place? do i need to do the same process with the labels too?

Private Sub cmdRecordInvoice_Click()

Dim i As Integer
Dim l As Single

On Error GoTo cmdError

   DoCmd.OpenForm "frmOrderView", acNormal
   
   Forms!frmOrderView.lblSelect.Visible = False
   Forms!frmOrderView.cmdEditView.Visible = False
   
   Forms!frmOrderView.lblReceive.Visible = False
   Forms!frmOrderView.cmdReceive.Visible = False
   
   Forms!frmOrderView.lblAmRec.Visible = False
   Forms!frmOrderView.cmdAmmendR.Visible = False

   Forms!frmOrderView.lblRecInv.Visible = True
   Forms!frmOrderView.cmdRecInv.Visible = True
   
   Forms!frmOrderView.lblAmmInv.Visible = True
   Forms!frmOrderView.cmdAmmendI.Visible = True

l = 60 'twips from edge
If Forms!frmOrderView.cmdEditView.Visible = True Then
    Forms!frmOrderView.cmdEditView.Left = l
    l = l + Forms!frmOrderView.cmdEditView.Width + 60
End If
If Forms!frmOrderView.cmdReceive.Visible = True Then
    Forms!frmOrderView.cmdReceive.Left = l
    l = l + Forms!frmOrderView.cmdReceive.Width + 60
End If
If Forms!frmOrderView.cmdAmmendR.Visible = True Then
    Forms!frmOrderView.cmdAmmendR.Left = l
    l = l + Forms!frmOrderView.cmdAmmendR.Width + 60
End If
If Forms!frmOrderView.cmdRecInv.Visible = True Then
    Forms!frmOrderView.cmdRecInv.Left = l
    l = l + Forms!frmOrderView.cmdRecInv.Width + 60
End If
If Forms!frmOrderView.cmdAmmendI.Visible = True Then
    Forms!frmOrderView.cmdAmmendI.Left = l
    l = l + Forms!frmOrderView.cmdAmmendI.Width + 60
End If
   
   Forms!frmMenu.Visible = False
   
cmdViewEdit_Click_Exit:
      Exit Sub

cmdError:
     MsgBox Error$
     Resume cmdViewEdit_Click_Exit
End Sub

 

[CJ_London]

butons don't have labels but if you are using them then you will need to code for it. If the widths of the labels are the same as the respective buttons then you can use the l variable otherwise you will need to use another one.

buttons are staying in the same place

Don't know why that should be - are you sure the code is being triggered?

 

[aron.ridgway]

Im not sure if the code is triggering or not? i have updated the and added the labels into the code. As if i manually change the size on the form it adjusts the button and the label equally.

I have also attached a screen shot of my form which shows the missing gap that i want to eradicate.

Private Sub cmdRecordInvoice_Click()

Dim i As Integer
Dim l As Single

'On Error GoTo cmdError

   DoCmd.OpenForm "frmOrderView", acNormal
   
   Forms!frmOrderView.lblSelect.Visible = False
   Forms!frmOrderView.cmdEditView.Visible = False
   
   Forms!frmOrderView.lblReceive.Visible = False
   Forms!frmOrderView.cmdReceive.Visible = False
   
   Forms!frmOrderView.lblAmRec.Visible = False
   Forms!frmOrderView.cmdAmmendR.Visible = False

   Forms!frmOrderView.lblRecInv.Visible = True
   Forms!frmOrderView.cmdRecInv.Visible = True
   
   Forms!frmOrderView.lblAmmInv.Visible = True
   Forms!frmOrderView.cmdAmmendI.Visible = True

l = 60 'twips from edge
If Forms!frmOrderView.cmdEditView.Visible = True Then
    Forms!frmOrderView.cmdEditView.Left = l
    Forms!frmOrderView.lblSelect.Left = 1
    l = l + Forms!frmOrderView.cmdEditView.Width + 60
End If
If Forms!frmOrderView.cmdReceive.Visible = True Then
    Forms!frmOrderView.cmdReceive.Left = l
    Forms!frmOrderView.lblReceive.Left = 1
    l = l + Forms!frmOrderView.cmdReceive.Width + 60
End If
If Forms!frmOrderView.cmdAmmendR.Visible = True Then
    Forms!frmOrderView.cmdAmmendR.Left = l
    Forms!frmOrderView.lblAmRec.Left = 1
    l = l + Forms!frmOrderView.cmdAmmendR.Width + 60
End If
If Forms!frmOrderView.cmdRecInv.Visible = True Then
    Forms!frmOrderView.cmdRecInv.Left = l
    Forms!frmOrderView.lblRecInv.Left = 1
    l = l + Forms!frmOrderView.cmdRecInv.Width + 60
End If
If Forms!frmOrderView.cmdAmmendI.Visible = True Then
    Forms!frmOrderView.cmdAmmendI.Left = l
    Forms!frmOrderView.lblAmmInv.Left = 1
    l = l + Forms!frmOrderView.cmdAmmendI.Width + 60
End If
   
   Forms!frmMenu.Visible = False
   
'cmdViewEdit_Click_Exit:
     ' Exit Sub

'cmdError:
    ' MsgBox Error$
    ' Resume cmdViewEdit_Click_Exit
End Sub

 

[CJ_London]

with the buttons way over to the right, you'll need a bigger value for l

But since I can't see your form design I can't advise what value - if the bit which has the data is a subform then it will be the width of the subform control+60. If this form is a contious form then it will be the the value of left for the username control plu the width of that control + 60

Just to clarify.

You have a form called frmMenu where this code resides on a button on this form.

When the button is clicked, it opens another form called frmOrderView which is the one you have sent a picture.

If this is the case, the code is probably not being triggered because control has passed to the newly opened form. To test this, put your cursor on the docmd.openform line then hit F9 to set a breakpoint.

Then click your button to trigger the code, the VBA window will pop up on that line - then hit F8 to move to the next line - see if it goes to the new form or continues with the code below the breakpoint.

You can also ensure the buttons you are setting to not visible with this code are visible in the first place

 

[aron.ridgway]

The attached is the form in design view to give a better idea of all the buttons.
yes that is correct depending on the button click on the menu will depend on what buttons are hidden on the frmOrderView Form.

All the buttons are made visible on the form close. I have run through the code and it scrolls through the form open event and then comes back and runs through my button code.

is that what is should be doing?

 

[gemma-the-husky]

moving controls may be very curious to users. they get used to clicking in certain areas. I would grey them, or hide them, but I would not start moving buttons around, I think

 

[CJ_London]

have run through the code and it scrolls through the form open event and then comes back and runs through my button code.

is that what is should be doing?

Yes. I presume your control names are correct so I am at a loss as to why the code is not working

 

[Simon_MT]

Have you tried Arrange > Tabular and then setting the Buttons sizes accordingly. In theory the controls should shuffle along.

Simon